Hyperlink code shows Subscript out of range error vba excel

Question

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

Dan · Accepted Answer · 2015-08-17 14:56:54Z

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...

Collectives™ on Stack Overflow

Hyperlink code shows Subscript out of range error vba excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related