I have added images to an Excel worksheet and each image has a hyperlink to another worksheet in the same workbook. Unfortunately, if a user renames a destination worksheet, the hyperlink breaks and it currently has to be manually reset. I believe that I can deal with this situation automatically in VBA by deleting the hyperlink and recreating a new one on a Change event. However, is it possible to use the worksheet codename instead of its new name in the Hyperlink so that there's no need to change anything?
5
-
4Have you tried to define a name?Yossi Benagou– Yossi Benagou2022-11-05 19:15:08 +00:00Commented Nov 5, 2022 at 19:15
-
I want to let the users rename the destination worksheets to whatever they want and still have the hyperlinks work. At present, this doesn't happen because Excel doesn't update the hyperlink .SubAddress property when the name changes. I was hoping, therefore, that rather than the worksheet name in the hyperlink, I could use the worksheet CodeName which is 'hidden' and doesn't change.JC_RMB– JC_RMB2022-11-05 19:36:43 +00:00Commented Nov 5, 2022 at 19:36
-
3Please, make a research about a named range and you will better understand the above suggestion... Using such a name, no need to use the sheet name, anymore. The named range will include it and it will be updated even if the sheet name is changed.FaneDuru– FaneDuru2022-11-05 19:48:27 +00:00Commented Nov 5, 2022 at 19:48
-
Ah OK. I understand what you mean now. I've used named ranges elsewhere in the workbook and so I'll give it a shot.JC_RMB– JC_RMB2022-11-05 20:11:54 +00:00Commented Nov 5, 2022 at 20:11
-
That seems to have done the trick. Many thanks.JC_RMB– JC_RMB2022-11-06 00:30:19 +00:00Commented Nov 6, 2022 at 0:30
Add a comment
|
1 Answer
Activate a Sheet (Using Its Code Name) By Using a Shape
- If using hyperlinks is not mandatory, you could assign this macro to each of the pictures (shapes).
Sub ActivateSheet()
' The following two arrays need to have the same number of elements.
Dim PictureNames() As Variant
PictureNames = VBA.Array("Picture 1", "Rectangle 1") ' add more, adjust!
Dim SheetObjects() As Variant
SheetObjects = VBA.Array(Sheet2, Chart4) ' add more, adjust!
Dim pName As String
On Error Resume Next
' 'Sheet1' is the code name of the worksheet with the pictures. Adjust!
pName = Sheet1.Shapes(Application.Caller).Name
On Error GoTo 0
If Len(pName) = 0 Then Exit Sub ' not called from a shape on 'Sheet1'
Dim Index As Variant: Index = Application.Match(pName, PictureNames, 0)
If IsError(Index) Then Exit Sub ' doesn't exist in list (array)
' The arrays are zero-based, while the index is one-based.
SheetObjects(Index - 1).Activate
End Sub
Comments
