MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Hyperlink to a hidden sheet

  Asked By: Elliott    Date: Sep 19    Category: MS Office    Views: 4655

I have a file containing multiple sheets the first of which is an index
sheet. The other sheets are hidden. I want to be able to click on a
hyperlink in the index sheet that will send me to the hidden sheet and
open it.
Is this possible ?



6 Answers Found

Answer #1    Answered By: Chaths Massri     Answered On: Sep 19

I'm sure there are several ways to do this,
but I've never done the hyperlink  to a hidden  sheet.
What I usually do is use a selectionchange event in the
Index sheet. test to see if the selection is one of the specific cells (or
value, or...) ,
then use a macro to unhide the sheet  and select it.
You can then use a deactivate event (or the activate event for the index  sheet)
to hide the other sheet.
need help with this?

Answer #2    Answered By: Tarron Thompson     Answered On: Sep 19

it's better to create a VBA that "looks" into the cell and leaves
you to the hidden  sheet (unhiding it before).

I have a sheet  here where I created a menu like this. The old guys made a harder
solution, with ComboBox and white/hidden cells... so I tried hyperlinks, but
with hyperlinks I have noticied that my users was not able to update the menu
when needed. So, my solution was made this VBA. Users can create new sheets  and
the only work is add a new line on this simple menu.

And about the original solution, I keep only the "hide others" feature, but
changing the "For 2 to XX" to "For 2 to Sheets.Count", thinking on a error-free
solution when the user add more sheets.

Answer #3    Answered By: Vid Fischer     Answered On: Sep 19

Normally, the focus is immediately transferred to the hyperlink
address on clicking into a cell containing a hyperlink. Are we saying
there is no way of adding any vba to this cell that unhides the
hidden sheet  THEN sends you to the destination ?

Answer #4    Answered By: Daniel Costa     Answered On: Sep 19

One method of doing this would be to simply hide the Tabs so
it appears that the only available sheet  is the one that you are
presently in. This does not prevent a user from moving to another
sheet with keystrokes but hiding the Tabs does not require code.

Answer #5    Answered By: Grace Ellis     Answered On: Sep 19

The problem is that the "hide" functionality was DESIGNED to make the sheet
without removing it.
At least temporarily.
The hyperlink  REQUIRES an accessible location.
You might be able to use the hyperlink to link to some other object that could
then, in turn, run the VBA, but I think this works "cleaner":
Here's what I did:
I have a spreadsheet that has a tab called "Summary", and a whole bunch of data
In a module, I added the macros:
Option Explicit
Dim I
Sub Refresh_List()
Dim inx
inx = 2
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
inx = inx + 1
Cells(inx, 3) = Sheets(I).Name
End If
Next I
End Sub
Sub Hide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
If (UCase(Sheets(I).Name) <> "SUMMARY") Then
Sheets(I).Visible = False
End If
Next I
Application.ScreenUpdating = True
End Sub
' (for Debugging purposes only)
Sub Unhide_Sheets()
Application.ScreenUpdating = False
For I = 1 To Sheets.Count
Sheets(I).Visible = True
Next I
Application.ScreenUpdating = True
End Sub

In the VBA for the "Summary" sheet, I added:
Private Sub Worksheet_Activate()
End Sub
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
If (Target.Value <> "") And (Not Intersect(Range(Target.Address),
Range("C3:C65000")) Is Nothing) Then
Application.ScreenUpdating = False
Sheets(Target.Value).Visible = True
Cancel = True
Application.ScreenUpdating = True
End If
End Sub
If you run "Refresh_List", it will find all of the tab names and put them in a
list starting in cell C3.
Now, if you double-click on any of the tab names, it will unhide the tab and
select it.
When you select the "Summary" tab, it hides all the other sheets.
Seems to work pretty clean to me.
If you have formulas that you need to add to the Summary tab for each data tab,
you can have the macro insert the formula.

Answer #6    Answered By: Alisha Johnson     Answered On: Sep 19

"Are we saying there is no way of adding any vba to this
cell that unhides the hidden  sheet THEN sends you to the destination ?"

I say "NO, it's not impossible", but think with me: Since VBA will be necessary,
why don't we create a simple code that does ALL the service?? If we can do it,
our users will don't need to worry about hyperlinks... just put the sheetnames
and description. Today I had to create another sheet  like this, see my solution:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Dim S As String
Dim N As Integer
S = ActiveCell.Offset(0, -1).Value
For N = 2 To Sheets.Count
Sheets(N).Visible = Not MENU.Range("hideall").Value
Sheets(S).Visible = True
End Sub

Well done. This is all.

I am turning this file  available on the net, please find the link below to see
how it works:

Didn't find what you were looking for? Find more on Hyperlink to a hidden sheet Or get search suggestion and latest updates.