2

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:

=HYPERLINK("http://stprs553/tickets/list/single_ticket/293136","Ticket #293136")

Or, more specifically (should paint a clearer picture):

=HYPERLINK(CONCATENATE("http://stprs553/tickets/list/single_ticket/","Target_Cell_Data"),"Ticket #Target_Cell_Data")

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.

  • @Brian below seems to have the answer to your problem. Just beware that this code will run after the hyperlink has been created if the cell is changed. It will then create an even larger hyperlink. Even hitting F2 and ENTER without making a "change" will trigger the Worksheet_Change event. Might want to add a check that the Target.Characters.Count < 10 or some other number to prevent the repeated hyperlink creation. – Byron Wall May 15 '15 at 0:37
  • Good point! In testing, it appears that when the cell is updated with F2 and ENTER, it remains the same. Also, when a number value is typed over a currently-filled cell in Column D that was already made a hyperlink, it appears to update and work properly. Thanks for the heads up though! :) – Adam May 15 '15 at 0:46
  • The second part about updating sounds correct. I think the first part will break once you get the TextToDisplay to be something other than the number by itself. Target.Value will return TextToDisplay and if it includes text other than the number, you will get a new hyperlink. – Byron Wall May 15 '15 at 0:48
  • Once you implement a fix to change the cell text to "Ticket #nnnnnn", then I would expect @Byron's concern to be realized. Definitely want to add such a check... I'll include it in an edited answer – hBy2Py May 15 '15 at 0:48
  • That makes sense. I was able to verify that entering in "Ticket #" will break it, creating a repeating string of "Ticket #" hyperlinks within the cell. This should be a problem though as I can use a Header for "Ticket #" instead, and have users just enter in the numbers themselves, as there doesn't seem to be an issue with that. – Adam May 15 '15 at 0:56
1

I think the function call should be:

Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value

I just changed both occurrences of c to Target, which I think will do what you're describing. The 'Object required' error is being thrown because c hasn't been instantiated to anything -- you can't retrieve a member of something that's not an object.

EDIT, to address the cell text not changing:

I wondered if the cell text would remain just the number. The fix will look something like this, I think:

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 IsNumeric(Target) And Not IsEmpty(Target) Then ' This condition should avoid munging of already-existing links, though if any tickets might be non-numeric it won't work properly.
             Application.EnableEvents = False  ' Must turn off event handling
             Target.Hyperlinks.Add Anchor:=Target, Address:="http://stprs533/tickets/list/single_ticket/" & Target.Value, TextToDisplay:="Ticket #" & Target.Value
             Target.Formula = "Ticket #" & Target.Value ' Should actually change the displayed text
             Application.EnableEvents = True
        End If
    End If
End Sub

It's important to turn off event handling whenever you use VBA to edit the contents of a cell inside an event like this, otherwise you run the risk of an infinite loop. I should have thought of this initially; but, tt would seem that applying a hyperlink to the cell doesn't trigger Worksheet_Change.

Note that if you delete a hyperlink from a cell, the cell will remain formatted as 'underlined' and 'blue font color'. For your use case, where the column will always be either links to tickets or blank cells, this is probably acceptable?

  • That's definitely something I overlooked. Thank you! Now I am getting a hyperlink generated properly, which is excellent. However, the friendly name for the hyperlink remains as the number string even with the modifications. Is there a way to concatenate the value in TextToDisplay:=? – Adam May 15 '15 at 0:39
  • That revision is perfect. Thanks for all of your help Brian and @Byron. Much appreciated! – Adam May 15 '15 at 1:05
  • Glad it worked, @Adam! Accepting my answer is the perfect way to say 'thank you'. :-) – hBy2Py May 15 '15 at 1:06
  • @Adam, I revised the conditional slightly to add an IsEmpty test, which now prevents the mungy 'Ticket #Ticket #' thing from happening when you delete a cell. – hBy2Py May 15 '15 at 1:27

Your Answer

By clicking "Post Your Answer", you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.