I am working on a project that would contain data from multiple sites.
On the "Welcome" worksheet (the main page) there is a dropdown menu (form control combo box) that will select a site location (which then prompts a picture of the site to appear)
I also want a link to appear that will link the user to that sites data worksheet.
I have the following code in VBA:
This code works great if i make "static" links that appear all the time by going to the insert hyperlink button in the menu then choosing the worksheet while it is unhidden, then entering the code into the vba... the link will open all my hidden worksheets.
---But i don't want visible links for all the worksheets to show up all the time, I only want the link to the selected worksheet to show up (the site chosen from the dropdown menu)----
SOOO I created a dynamic link: =HYPERLINK("[.\]"&C4, B4)
B4 contains a vlookup dependent upon the output from the dropdown menu which returns the site name and therefore is what i use as the friendly name for the hyperlink. C4 is just text added to the site name in B4 so that it refers to the name of a worksheet (i.e. for the CATLETTSBURG site B4 contains "Catlettsburg" and C4 contains "#Catlettsburg!A1" )
This dynamic link works PERFECTLY when the worksheets are not hidden... however if I hide the worksheets it fails. I am very very new to VBA.... today was my first day playing with it. I was able to get it to work for the static links following Mr. Excel tutorials and a few other forums. I have a feeling that by tweaking some part of the VBA I can get it to work for the dynamic link but not sure what to do.
On the "Welcome" worksheet (the main page) there is a dropdown menu (form control combo box) that will select a site location (which then prompts a picture of the site to appear)
I also want a link to appear that will link the user to that sites data worksheet.
I have the following code in VBA:
PHP:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Dim strSheet As String If InStr(Target.Parent, "!") > 0 Then strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1) Else strSheet = Target.Parent End If Sheets(strSheet).Visible = True Sheets(strSheet).Select End SubPrivate Sub Worksheet_Activate() Dim ws As Excel.Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = (ws.Name = Me.Name) Next End Sub
This code works great if i make "static" links that appear all the time by going to the insert hyperlink button in the menu then choosing the worksheet while it is unhidden, then entering the code into the vba... the link will open all my hidden worksheets.
---But i don't want visible links for all the worksheets to show up all the time, I only want the link to the selected worksheet to show up (the site chosen from the dropdown menu)----
SOOO I created a dynamic link: =HYPERLINK("[.\]"&C4, B4)
B4 contains a vlookup dependent upon the output from the dropdown menu which returns the site name and therefore is what i use as the friendly name for the hyperlink. C4 is just text added to the site name in B4 so that it refers to the name of a worksheet (i.e. for the CATLETTSBURG site B4 contains "Catlettsburg" and C4 contains "#Catlettsburg!A1" )
This dynamic link works PERFECTLY when the worksheets are not hidden... however if I hide the worksheets it fails. I am very very new to VBA.... today was my first day playing with it. I was able to get it to work for the static links following Mr. Excel tutorials and a few other forums. I have a feeling that by tweaking some part of the VBA I can get it to work for the dynamic link but not sure what to do.
Last edited: