hyperlink to hidden sheet - in 2010

Why not create a macro that unhides and selects the sheet, and assign it to the hyperlink?

can you give me an example of 1 hyperlink in 1 cell pointing to an other sheet ?
gowflow

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

Select allOpen in new window

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.

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

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

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

Brilliant, I am no longer the only person to do this!

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

Select allOpen in new window

byundt
nice touch

gowflow

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

Select allOpen in new window

hasa55 have you had a chance to try out the proposed solutions ?
gowflow

teylyn, some split between goflow and byundt.

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

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

Select allOpen 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

Select allOpen in new window

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

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

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