using Hyperlinks Podcast #1729 produces an ERROR on the 1st Woksheets line, why?

[Raymond1]

using a horizontal list entitled "Training2019" with hyperlinks to 3 hidden worksheets using code:

Private Sub Worksheet_FollowHyperlink (ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
MySheet = Left(LinkTo, WhereBang-1)
Worksheets (MySheet).Visibe = True **ERROR Here
Worksheets (MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(mySheet).Rang(MyAddr).Select
EndIf
End Sub


each sheet also has the following code:
Private Sub Worksheet_FollowHyperlink(ByVal Tareget A Hyperlink)
Worksheets ("Training2019").Select
Target.Parent.Worksheet.Visible = False
End Sub

what am I doing wrong?

 

[MARK858]

That means that more than likely there is something wrong with the sheetname. Check that mySheet exactly matches your sheets name including any leading/trailing spaces.

If that exactly matches then you will have to do the same with MyAddr.

Edit: not sure what you are saying with your last posts.

 

[Raymond1]

was trying to provide the value, which is now WhereBang=15, but still hqs RUNTime error 9

 

[MARK858]

The error you are getting would still imply that there is something wrong with the sheetname (it must be exact including the spaces) or the range. Use Debug.Print to see exactly what mySheet is producing.

 

[Fluff]

Another thought, are any of your sheet names purely numeric (ie 15 or 22)?

 

[Raymond1]

No they are 4 alphabetically named sheets;
Run-time error '9'
Subscript out of range

 

[Fluff]

What is the value of "MySheet" when you get the error? and are you sure that you have a sheet with that Exact name?

 

[Raymond1]

the value is "TIG Training" (ie. the label of 1 of my 3 sheets); the name of my Master Sheet is "Training2019" .... just a thought, would the file extension .xlsb have anything to do, or perhaps I am missing a step after I hit "RUN"

 

[Fluff]

Unhide & Select the "TIG Training" sheet, then run this code

Sub chk()
Debug.Print "|" & ActiveSheet.Name & "|", Len(ActiveSheet.Name)
End Sub

Look in the Immediate window, below the main code window (Ctrl G will bring it up if not visible) & paste what is in there to the thread

 

[Raymond1]

|TIG Training| 12
|TIG Training| 12
|TIG Training| 12
|TIG Training| 12
|TIG Training| 12
|TIG Training| 12

 

[Fluff]

Ok, it's because you sheet has spaces in the name.
With your code MySheet will return 'TIG Training' rather than TIG Training.
Do all you sheets have spaces in the name?