Hyper links when source worksheet hidden

osaben

Board Regular
Joined
Mar 17, 2010
Messages
55
Can anyone help me please?

I have a workbook with multiple worksheets. Worksheet#1 is the one that the user will beactively using. It has hyperlink to other worksheets within the sameworkbook. These other worksheets areinformation only. When I hide the otherworksheets, the hyperlinks stop working on worksheet #1.
Is there a way to correct this so that the user only sees worksheet#1 but when they click on a hyperlink it also them to see the other workbook?

Thanks!
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
UDATE-- I understand that VBA code is necessary. I a getting the below error.

Can anyone help?

NOTE- The link text and the name of the worksheet are not the same.
 
Last edited by a moderator:
Upvote 0
There is a way for that(at least). Do you need the active sheet to be visible on the status bar? Or it could be hidden since the status bar can be hidden with vba.

eLy
 
Upvote 0
I use this on my project. I hope I can explain it to you:

I have this:
to "force the use of macros I have a landing page named "Start". th code goes into thisworkbook

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)    
'Step 1:  Declare your variables
    Dim ws As Worksheet


'Step 2: Unhide the Starting Sheet
    Sheets("START").Visible = xlSheetVisible


'Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 4: Check each worksheet name
    If ws.Name <> "START" Then
 
'Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If
   
'Step 6:  Loop to next worksheet
    Next ws


'Step 7:  Save the workbook
    ActiveWorkbook.Save


End Sub


Private Sub Workbook_Open()
'Step 1:  Declare your variables
    Dim ws As Worksheet


'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible


'Step 5:  Loop to next worksheet
    Next ws


'Step 6:  Hide the Start Sheet
    Sheets("START").Visible = xlVeryHidden
    Sheets("SHEET!").Visible = xlVeryHidden
End Sub


This goes to the sheet you want to be active, or never hidden.

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).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(LinkTo, WhereBang + 1)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub



This goes to all the sheets you want to be hidden (xlveryhidden)

Code:
Private Sub Worksheet_Deactivate()ThisWorkbook.Sheets("HIDDEN SHEET").Visible = False 'hide
End Sub




Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   Worksheets("ACTIVE SHEET NOT HIDDEN").Select
   Target.Parent.Worksheet.Visible = False
End Sub

I hope this helps. I found the code online as well and came to adapted to my needs with time.

best regards,
eLy
 
Upvote 0
Thank You for the reply. I am using the below code on the worksheet where the hyperlink exists. I get a subscript out of range error. when I try to debug,it seems to be the bolded line below. Any ideas?

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).Visible = True
Worksheets(MySheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(MySheet).Range(MyAddr).Select
End If

End Sub







I use this on my project. I hope I can explain it to you:

I have this:
to "force the use of macros I have a landing page named "Start". th code goes into thisworkbook

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)    
'Step 1:  Declare your variables
    Dim ws As Worksheet


'Step 2: Unhide the Starting Sheet
    Sheets("START").Visible = xlSheetVisible


'Step 3: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 4: Check each worksheet name
    If ws.Name <> "START" Then
 
'Step 5: Hide the sheet
    ws.Visible = xlVeryHidden
    End If
   
'Step 6:  Loop to next worksheet
    Next ws


'Step 7:  Save the workbook
    ActiveWorkbook.Save


End Sub


Private Sub Workbook_Open()
'Step 1:  Declare your variables
    Dim ws As Worksheet


'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible


'Step 5:  Loop to next worksheet
    Next ws


'Step 6:  Hide the Start Sheet
    Sheets("START").Visible = xlVeryHidden
    Sheets("SHEET!").Visible = xlVeryHidden
End Sub


This goes to the sheet you want to be active, or never hidden.

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).Visible = True
        Worksheets(MySheet).Select
        MyAddr = Mid(LinkTo, WhereBang + 1)
        Worksheets(MySheet).Range(MyAddr).Select
    End If
End Sub



This goes to all the sheets you want to be hidden (xlveryhidden)

Code:
Private Sub Worksheet_Deactivate()ThisWorkbook.Sheets("HIDDEN SHEET").Visible = False 'hide
End Sub




Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   Worksheets("ACTIVE SHEET NOT HIDDEN").Select
   Target.Parent.Worksheet.Visible = False
End Sub

I hope this helps. I found the code online as well and came to adapted to my needs with time.

best regards,
eLy
 
Upvote 0
I am terrible to explain stuff. I added a sample of the code and what it does to see if this is what you're looking:
I added an extra code since you need to save the file as macro book it only allows to save as macro and not the other formats. you can delete it if you dont want/need it.

sample file: http://s000.tinyupload.com/index.php?file_id=26661630851552036358


best regards,
eLy
 
Upvote 0
Try
Code:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
LinkTo = Target.SubAddress
WhereBang = InStr(1, LinkTo, "!")
If WhereBang > 0 Then
mysheet = [COLOR=#ff0000]Replace(Left(LinkTo, WhereBang - 1), "'", "")[/COLOR]
Worksheets(mysheet).Visible = True
Worksheets(mysheet).Select
MyAddr = Mid(LinkTo, WhereBang + 1)
Worksheets(mysheet).Range(MyAddr).Select
End If

End Sub
 
Upvote 0
Fluff,
Your suggested code was just what was needed. Thank You.
Now --- Once I am on the hidden worksheet, I have a hyperlink there to send me back to the main worksheet. Below is the code I have attached to thesehidden worksheets. I was trying toreturn to the main worksheet (ASC606) and re-hide the worksheet. It is not working. I am getting a syntax error that seems to be associatedwith the bolded text.
Private Sub Worksheet_FollowHyperlink(ByVal Target AsHyperlink)
Worksheets("ASC606).Select
Target.Parent.Worksheet.Visible = False

End Sub

 
Upvote 0
You're missing the closing " at the end of the sheet name
 
Upvote 0

Forum statistics

Threads
1,213,583
Messages
6,114,478
Members
448,574
Latest member
bestresearch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top