We help IT Professionals succeed at work.

hyperlink to hidden sheet - in 2010

hasa55
hasa55 asked
on
i have a spreadsheet that has a number of hyperlins to other sheets in the file - i want to have all sheets hidden except my main working sheet.

So if i have a summary sheet with a number of hyperlinks i want to click on a hyperlink that i have linked - but as it is hidden it does not work - i want to be able to click my hyperlink and it opens my sheet (keeping my main sheet open) after i have finished with the sheet i have a hyperlink that goes back to my main sheet - i want this to close the sheet i am leaving to go back to my main sheet. hope thisa makes sense.

Regards

h
Comment
Watch Question

Commented:
Why not create a macro that unhides and selects the sheet, and assign it to the hyperlink?
gowflowPartner
BRONZE EXPERT

Commented:
can you give me an example of 1 hyperlink in 1 cell pointing to an other sheet ?
gowflow
Partner
BRONZE EXPERT
Commented:
Is this what you want ?
gowflow
hyperlinks.xls
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
If the worksheet name might contain a space, then you will need to remove the single quotes from it in gowflow's macro:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim Sht As String
Sht = Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1)
If Left(Sht, 1) = "'" Then Sht = Mid(Sht, 2, Len(Sht) - 2)
If Sheets(Sht).Visible <> xlSheetVisible Then
    Sheets(Sht).Visible = xlSheetVisible
    Target.Follow
End If

End Sub

Open in new window

BRONZE EXPERT

Commented:
Nice, gowflow - I've always wanted to see a use for the FollowHyperlink event!

hasa55, it might be worth asking gowlflow to slightly change his code so that Main is hidden while you're looking at a subsidiary sheet. This would force users to click on the link to go back to Main. Otherwise they may simply click on Main's tab and so leave the subsidiary sheet visible.

Regards,
Brian.
gowflowPartner
BRONZE EXPERT

Commented:
you can simply ask me no need to go thru intermediary !!!
here it is
thanks byundt for your note !
gowflow
hyperlinks.xls
BRONZE EXPERT

Commented:
gowflow, I didn't want to waste your time if it's not what the boss actually wanted!
gowflowPartner
BRONZE EXPERT

Commented:
I mistaken you with the asker !!!! sorry !!!!
can;t beleive went doing it !!! this must be recorded for history of blindness !!!
hahaahhaah
lets wait for the boss !!!

.. I still don't beleive it ...
gowflow
BRONZE EXPERT

Commented:
Brilliant, I am no longer the only person to do this!
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
You can replace all the subs in the worksheet code panes with this one in the ThisWorkbook code pane. It uses the same logic as gowflow has been posting, only with a Workbook_SheetFollowHyperlink sub that hides the source and unhides the target.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim Sht As String
Sht = Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1)
If Left(Sht, 1) = "'" Then Sht = Mid(Sht, 2, Len(Sht) - 2)
If Sheets(Sht).Visible = False Then
    Sheets(Sht).Visible = True
    Target.Follow
End If
Sh.Visible = xlSheetHidden
End Sub

Open in new window

gowflowPartner
BRONZE EXPERT

Commented:
byundt
nice touch

gowflow
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
When tracing the code, I noticed that it looped recursively. Here is the fix (turning off events before following the hyperlink):

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim Sht As String
Sht = Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1)
If Left(Sht, 1) = "'" Then Sht = Mid(Sht, 2, Len(Sht) - 2)
If Sheets(Sht).Visible <> xlSheetVisible Then Sheets(Sht).Visible = xlSheetVisible
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True
Sh.Visible = xlSheetHidden
End Sub

Open in new window

gowflowPartner
BRONZE EXPERT

Commented:
hasa55 have you had a chance to try out the proposed solutions ?
gowflow
BRONZE EXPERT

Commented:
teylyn, some split between goflow and byundt.
gowflowPartner
BRONZE EXPERT

Commented:
well teylyn you are knowlegable enough to know the detials when it comes to this zone and can judge by yourself who deserve what.

now if we are in a rat race obviously my solution is the first and byundt added some poishing and other options that the asker maybe did not request as such.
now from my point of view for sure I do not beleive we are in a rat race and it is not the first solution that always need to be taken into concideration as beeing 'THE SOLUTION' for sure byundt had a contribution in this solution and will leave it up to your best judgement to know how to deal with this.

gowflow
byundtMechanical Engineer
SILVER EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
There is one additional refinement that ought to be made. If the worksheet name contains a single quote, then it will be doubled in the hyperlink. The cure is:

'Put this code in ThisWorkbook code pane. It won't work at all if installed anywhere else.
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Dim Sht As String
Sht = Replace(Mid(Target.Name, 1, InStr(1, Target.Name, "!") - 1), "''", "'")     'Embedded single quotes in sheet names are doubled in hyperlinks
If Left(Sht, 1) = "'" Then Sht = Mid(Sht, 2, Len(Sht) - 2)
If Sheets(Sht).Visible <> xlSheetVisible Then Sheets(Sht).Visible = xlSheetVisible
Application.EnableEvents = False
Target.Follow
Application.EnableEvents = True
Sh.Visible = xlSheetHidden
End Sub

Open in new window


Regarding credit for the solution, all of my contributions were mere tweaks to gowflow's basic code--so gowflow deserves the majority, if not all of the points.

Brad
Hopefully this doesn't look too simple, but I think it meets your need... First, I would use an image, or a button and assign it to the following macro, then I would copy and paste the button to every sheet other than your main one:
Sub Hide_()

Dim HideSheet As String
Dim MainSheet As String

MainSheet = "Main" 'Enter the name of your "dashboard" between the quotes
HideSheet = ActiveWorkbook.ActiveSheet.Name

Sheets(HideSheet).Visible = False
Sheets(MainSheet).Activate


End Sub

Open in new window


Once you've done that, you will want to place a separate button (your "go to" button) on
your Dashboard / Summary sheet.  If you have the option, next to each Sheet Description on your summary, just type the name of the sheet and turn the text white if you don't want to see it...
Then click on the name of the sheet, and click the button your summary that you just assigned to this macro:

Sub Un_Hide()

Dim GoToSheet As String

GoToSheet = Selection.Value
Sheets(GoToSheet).Visible = True
Sheets(GoToSheet).Activate


End Sub

Open in new window


Seems somewhat contrived, but it will get the job done exactly how you want it.
gowflowPartner
BRONZE EXPERT

Commented:
hasa55
Did you try the proposed solutions ? Are you still intrested in getting solution for this longstanding issue ? We would appreciate your input.
gowflow
TracyVBA Developer
BRONZE EXPERT

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.