Workbookname as Hyperlinkadress

[Maxxim]

Hello,
I want to change the Hyperlink with VBA.
I need the adress: "Workbookname - Prozessparameter_2K!A1"
But when I start the code the workbookname doesn't take.. the adress that i get when I start the code is: wbNameProzessparameter_2K!A1

In my workbook I have several sheets that are hidden. When I show the sheets with a tick, the link on the text field has to change so that I can get to the newly shown page with the text field.

Sub Texfeld()
If ActiveWorkbook.Sheets("Prozessparameter_2K").Visible = True Then
Dim wbName As String
wbName = Application.ActiveWorkbook.Name
ActiveSheet.Shapes.Range(Array("TextBox 30")).Select
Selection.ShapeRange.Item(1).Hyperlink.Delete
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1), Address:="" & wbName
Selection.ShapeRange.Item(1).Hyperlink.Address = "wbName" & "Prozessparameter_2K!A1"
End If
End Sub

Thank you.

 

[HaHoBe]

Hi Maxxim,

wbName is defined as String variable at the start.

Selection.ShapeRange.Item(1).Hyperlink.Address = "wbName" & "Prozessparameter_2K!A1"

This line is interpreted as a string for wbName (not as the variable you have filled before) and thus you will get the result wbNameProzessparameter_2K!A1.

You should change the line to read

Selection.ShapeRange.Item(1).Hyperlink.Address = wbName & "Prozessparameter_2K!A1"

to get the result you expect.

Ciao,
Holger