Using variables to create hyperlinks in a macro

[pgzr]

Hi, all!

I am attempting to build a workbook for use by our HR director. In the section I'm working on now, I'm trying to create a macro to do the following:

- Allow user to enter New Employee name into an Input Box
- Create a new worksheet using the New Employee name as the
Sheet Name
- Add the New Employee name to the Directory page
- Add a hyperlink to the cell on the Directory Page that will jump to the
New Employee name sheet that was just created.

Most of my macro is working great! My input box comes up, creates and renames a new sheet, and adds the name to the directory page.

However, when it comes to creating the hyperlink, I have the following line of code in my macro:

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", _
SubAddress:= "'NewName'!A1", TextToDisplay:=NewName

(NewName is the name of my variable)

When I try to click the hyperlink, I get a "Reference is not valid" error. I got the code by recording a macro that creates a hyperlink, then changed the subaddress and texttodisplay fields to use my variable name.

I'm thinking this should be something simple to fix, but I've tried all kinds of variations on the above code (including changing the macro so the sheet is created before the hyperlink!), and can't seem to get it right.

Can anyone help me? Thanks so much!

 

[Russell Hauf]

Try something like this:

SubAddress:= "'" & NewName & "'A1"

HTH,

 

[pgzr]

Russell,

Thanks for the tip -- that did the trick!

 

[harper86]

Hi I have run into a similar issue. For some reason the hyperlink generated by the following code says the reference is not valid:

Range("rngname").Offset(j, 0).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & list(j) & "'A1", TextToDisplay:=list(j)

So I have a list of all the worksheet names, and I;m trying to build up a summary page which would have hyper links to each sheet in the workbook.

I have literally tried everything to fix this. What am I doing wrong?

Thank you very very much.

 

[harper86]

Hi Ive managed to fix the issue somewhat by using:
ActiveSheet.Hyperlinks.Add ActiveCell, "", Sheets(list(j)).Name & "!A1", aplist(j)

However, when the variable name contains special characters such as a - eg. LM 0087-01, the hyperlinking does not work, as in once the code is done executing when I go to the sheet and click on the cell which says LM 0087-01, it says reference is not valid, although I have a worksheet in the same workbook whose name is LM 0087-01.

Please help me out.

Thanks!

 

[Gideon1973]

Hi Ive managed to fix the issue somewhat by using:
ActiveSheet.Hyperlinks.Add ActiveCell, "", Sheets(list(j)).Name & "!A1", aplist(j)

However, when the variable name contains special characters such as a - eg. LM 0087-01, the hyperlinking does not work, as in once the code is done executing when I go to the sheet and click on the cell which says LM 0087-01, it says reference is not valid, although I have a worksheet in the same workbook whose name is LM 0087-01.

Please help me out.

Thanks!

I know this is an old post...but what happens if you were turn on the macro recorder, and then manually create that hyperlink with the special characters? Does the results in VBA look like they are URL encoded?

I find it very helpful to do a few little steps in a controlled manner using the VBA macro recorder, stop the macro from recording, and then dissect the resulting VBA code. That way, I can make the process repeatable and/or dynamic by seeing how VBA "writes" the code.