CODEWITHSUNDEEP
excelhyperlinkvba
vinit
vinit

Reputation: 85

Hyperlink code shows Subscript out of range error vba excel

I have a hyperlink vba code that activates a hidden worksheet when clicked. But for some reason every time I run I get an error saying

"Subscript out of Range"

at the line of code Worksheets(MySheet).Visible = xlSheetVisible

The code is

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Linkto = Target.SubAddress
Name = InStr(1, Linkto, "!")
If Name > 0 Then
    MySheet = Left(Linkto, Name - 1)
    Worksheets(MySheet).Visible = xlSheetVisible
    Worksheets(MySheet).Select
    MyAddr = Mid(Linkto, Name + 1)

    Worksheets(MySheet).Range(MyAddr).Select
 End If
End Sub

Upvotes: 0

Views: 1311

Answers (1)

Dan
Dan

Reputation: 163

You should avoid using variable names such as "Name" which could be reserved words. In this particular case, I guess Name will refer to the current worksheet (The one on which the Hyperlink is set)

Try the same code by replacing the variable "Name" by, for example "myName"

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

  Linkto = Target.SubAddress
  myName = InStr(1, Linkto, "!")
  If myName > 0 Then

      MySheet = Left(Linkto, myName - 1)
      Worksheets(MySheet).Visible = xlSheetVisible
      Worksheets(MySheet).Select
      MyAddr = Mid(Linkto, myName + 1)
      Worksheets(MySheet).Range(MyAddr).Select

  End If
end sub

and also, as an advice,

  • Use "OPTION EXPLICIT" at the beginning of your VBA to force you explicit declaration of variables
  • use variable names in line with the content. You call the variable "Name" but you actually store in it the position of the exclamation mark in the hyperlink target... kind of confusing...

Hope that fixes your issue...

Upvotes: 2

Related Questions