Justus Beek


I would like to add a hyperlink that refers to a cell within a worksheet and I would like to do this dynamically.

As an example:

Sheets("Sheet1").Hyperlinks.Add Anchor:=Sheets("sheet1").Cells(1,2), Address:="", SubAddress:= _
"'sheet2'!A1", TextToDisplay:="Back <<"

This works.

But now I would like to assign the subaddress dynamically. I have tried several things but I cannot get it working.

so: "'Sheets2'!A1" should be replaced by a dynamic assignment.

Anybody any ideas



Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

MyLady


Hey,

Can you show some examples of what you have tried

Best Regards

Cathrine






Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

Justus Beek

Sheets("P&L").Hyperlinks.Add Anchor:=Sheets("P&L").Cells(1,1), Address:="", SubAddress:= _
"R3C3"

Sheets("P&L").Hyperlinks.Add Anchor:=Sheets("P&L").Cells(1,1), Address:="", SubAddress:= _
Cells(5,5)

I tried a lot of things. The subaddress needs to be a string. If I put there "A1:B2" this will work, but it has to be done dynamically...





Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

Cringing Dragon

Have you tried using the Address property That returns the address of the range as a string.

Something like:

Code Snippet
Sheets("P&L").Hyperlinks.Add Anchor:=Sheets("P&L").Cells(1, 1), _
Address:="", SubAddress:=Cells(5, 5).Address

If a post answers your question, please mark it as the solution.






Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

Justus Beek

thanks!

This solved it. I tried so many things... New it had to be something simple like this ;-)





Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

jcmonc

Exactly how did you create the field. I have a form I put the vba code in for the field so that it would dynamically change the field to a hyperlink. However in testing as data goes in I get a runtime errorr "1004"

application-defined or object-defined error.





Re: Creating a dynamic hyperlink to a worksheet cell in Excel using VBA

Cringing Dragon

The code in the original post placed the hyperlink in a cell on a worksheet. No forms or fields involved.

I think a hyperlink can only be placed in a cell or on a Shape object.

So if your "field" was a Text Box shape (ie from the drawing Toolbar, not the textbox control) you could add the hyperlink as follows:

Code Block

Sheets("Sheet1").Hyperlinks.Add _
Anchor:=Sheets("Sheet1").Shapes("Text Box 1"), _
Address:="", SubAddress:=Cells(5, 5).Address

Note that applies a hyperlink to the textbox shape (so that if the box is clicked, the hyperlink is followed). It doesn't actually add any text to the textbox.

Alternatively, you could add a hyperlink to the click event of a control using the FollowHyperlink Method, see:

http://msdn2.microsoft.com/en-us/library/aa195736(office.11).aspx