I'm relatively new to VBA code within Excel, so please forgive me if the answer is simple (I expect it usually is). I'm using Excel 2010, for reference. I'm trying to accomplish the following, with the proceeding script:
If a number string (any value) is entered into any cell within Column D, I need to have this automatically converted into a Hyerlink which would append a certain address string before the target cell's value, and spit out a friendly name that includes the cell's value.
In other words, here's the example Excel function I'm trying to have replicated with a macro:
Or, more specifically (should paint a clearer picture):
293136 is the example number string that I want to have a user be able to type into any cell within Column D. When they do, I want to have this cell converted into a Hyperlink, using the number string as the base data, appending the rest of the URL to the left of the cell value, then create a friendly name that concatenates "Ticket #" with the number string from the cell's value. I don't want my users to have to learn Excel formulas in order to have a reference link, or waste time having to copy both ends of the function to wrap around the cell data.
Here's what I have so far in VBA:
Private Sub Worksheet_Change(ByVal Target As Range) Dim rangeLimit As Range Set rangeLimit = Range("D:D") If Not Intersect(rangeLimit, Target) Is Nothing Then If Target <> "" Then Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & c.Value, TextToDisplay:="Ticket #" & c.Value, Target.Value End If End If End Sub
Here's the problems I'm running into:
1.) If I try to run the formula as is, I get a Compile Error.
2.) If I remove the
, Target.Value end of the Hyperlink string, I get an Error 424, "Object Required" error.
What am I doing wrong, and/or how can I create a macro to perform the desired function? A good solid hour of Google searching and taking a look through some StackOverflow posts brought me to what I have now, which still doesn't work.