VBA Trouble

[Nate199230]

I'm extremely new to this and I am having issues with this code. I can't seem to figure out what needs to be done. I tried following along with a video and when he runs it, it doesn't pop up the same thing as mine. The line that pops up when I press debug is the one written in yellow since I don't see a highlight option. I am trying to get my hyperlinks on my page1 to work when the other pages are hidden. Thank you in advance for any help.


Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
LinkTo = Target.SubAdress
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
MySheet = Left(LinkTo, WhereBang - 1)
Worksheets(MySheet).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)

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


End Sub

 

[NoSparks]

Could do it this way

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    Dim MySheet As String
    Dim MyAddr As String
   
    MsgBox Target.SubAddress    ' for visual verification only
   
    MySheet = Split(Target.SubAddress, "!")(0)
    MyAddr = Split(Target.SubAddress, "!")(1)
   
    With Sheets(MySheet)
        .Visible = True
        .Select
        .Range(MyAddr).Select
    End With

End Sub

 

[Nate199230]

Could do it this way

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    Dim MySheet As String
    Dim MyAddr As String
  
    MsgBox Target.SubAddress    ' for visual verification only
  
    MySheet = Split(Target.SubAddress, "!")(0)
    MyAddr = Split(Target.SubAddress, "!")(1)
  
    With Sheets(MySheet)
        .Visible = True
        .Select
        .Range(MyAddr).Select
    End With

End Sub

I ended up figuring out that i had to change to:

Private Sub Worksheet_FollowHyperlink(ByVal Target As HyperLink)
linkto = Target.SubAddress
wherebang = InStr(1, linkto, "!")
If wherebang > 0 Then
mysheet = Replace(Left(linkto, wherebang - 1), "'", "")
Worksheets(mysheet).Visible = True
Worksheets(mysheet).Select
MyAddr = Mid(linkto, wherebang + 1)

Worksheets(mysheet).Range(MyAddr).Select
End If


End Sub

and on all other worksheets I have:

Private Sub Worksheet_FollowHyperlink(ByVal Target As HyperLink)
Worksheets("DashBoard").Select
Target.Parent.Worksheet.Visible = False
End Sub

Only issue I am now trying to figure out is, When I go to one page that has two hyperlinks attached, both hyperlinks will go back to dash board instead of the "customize page" hyperlink actually going to Customize Page. Any idea on how to fix it? Much appreciated for your time.

 

[NoSparks]

you are ignoring the subaddress and using Worksheet("DashBoard") instead.

 

[Nate199230]

I’m using that to make the page hide again when you click dashboard. If I want that to still happen, and then have the customize button bring you to the customize page, how would I write the code? Would I delete the one I’m using now and use a different one?

 

[NoSparks]

I’m using that to make the page hide again when you click dashboard.

I don't know what you mean when you say click dashboard.

and then have the customize button bring you to the customize page,

First hearing of a customize button and don't know where it is.


Based on this from post #3

and on all other worksheets I have:

Private Sub Worksheet_FollowHyperlink(ByVal Target As HyperLink)
Worksheets("DashBoard").Select
Target.Parent.Worksheet.Visible = False
End Sub

Only issue I am now trying to figure out is, When I go to one page that has two hyperlinks attached, both hyperlinks will go back to dash board instead of the "customize page" hyperlink actually going to Customize Page. Any idea on how to fix it?

I would use this, but must admit really not knowing what you're working with

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim MySheet As String
    Dim MyAddr As String
   
    MsgBox Target.SubAddress    ' for visual verification only
   
    MySheet = Replace(Split(Target.SubAddress, "!")(0), "'", "")
    MyAddr = Split(Target.SubAddress, "!")(1)
   
    With Sheets(MySheet)
        .Visible = True
        .Select
        .Range(MyAddr).Select
    End With

    Target.Parent.Worksheet.Visible = False

End Sub

The replace function is being used to eliminate the apostrophes that would be in the subaddress formula if the sheet name should happen to contain any spaces.

 

[Nate199230]

sorry, I should of guessed a visual would help better. The back to dashboard is just a hyperlink set up on all sheets except for sheet 1 (dashboard). The customize button, is just another hyperlink to go from the gift shop to a page of all customizations we offer.