Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Hide sheets and hyperlink

0

Hello... I need help regarding hyperlink and hidden sheets.. In my excel file, i have 14 sheets, the first sheet is the homepage, and the rest should be hidden. What would be my formula or code if i want to hide the sheets and hyperlink them? And if i will click the hyperlink the sheets will show and if i go back in the home page, it will be hidden again.... Thank you.

Answer
Discuss

Answers

2

Short code. Long explanation. Start with sheet names.

Every sheet (there are pivot sheets and chart sheets besides worksheets) has 2 names, the tab name, which the user can change from Excel's UI, and the CodeName which can only be changed in the VB Editor's properties. When a sheet is created both names are the same.

In the attached workbook I named the Home tab "Home" and gave it the CodeName "Dashboard". Therefore Worksheets("Home").Name and Dashboard.Name will both return "Home" while Worksheets("Home").CodeName and Dashboard.CodeName will both return "Dashbard". Observe the different syntax. The point is that the user can change the name of the tab to whatever comes to his mind but the code will still find the sheet by its CodeName.

In the attached workbook there are 4 buttons which I numbered carefully. Button Cmd00 has the caption "Tab 0", Cmd01 has the caption "Tab 01" etc. Later, you will replace the captions with your real sheet names but the button numbers and their significance will remain. So, pay attention to these numbers.

In the codesheet for Dashboard there are 4 procedures named like this one.

Private Sub Cmd00_Click()
    ' 215
    GoToTab 0
End Sub

Each of these procedures responds to the command button in its declaration. The above one will run when Cmd00 is clicked. It will call the procedure GoToTab with the parameter 0. The 4 procedures are all identical except for the button name and the parameter. The parameter is always the same as the the serial number in the button's name. As you add more sheets to the system you will need to create more procedures like these four:- Copy > Paste > Change button number > Change parameter = Done. It's easy but must be exact.

The procedure GoToTab is located in a standard code module, TXL_5321 in the attached workbook. It will make the tab identified by the parameter visible and activate it. To be able to do that it needs to know the name of the worksheet. Actually, the code uses the CodeName so that the tab name doesn't matter (except that it's in the button caption which the user sees). The CodeName is identified by the calling argument and extracted from this string in the GoToTab procedure:-

Const CodeNames     As String = "Sheet2,Sheet3,Sheet4,Sheet5"

Here you need to list the CodeNames of all your sheets (except Dashboard). The string is split on the commas (be sure not to insert any blank spaces next to the commas) into a 0-based array. So, CodeName(0) = "Sheet2" and 0, of course, is the argument with which the sub was called when Cmd00 was clicked, and the caption of Cmd00 is "Tab 0". Therefore "Sheet2" is the CodeName of "Tab 0".

In the attached workbook, the 4 tabs are VeryHidden. VeryHidden is different from Hidden. A Hidden sheet can be made visible by the user from Excel's UI. A VeryHidden sheet can only be made visible by changing the Visible property in the VB Editor. The user can't even know that it exists without looking into the VBE.

When you click on one of the buttons on the Home tab the chosen tab will be made visible. On it you will see a button. All sheets have copies of the same button. It's called CmdHome, same name on all sheets. Each sheet has this code in its code module - same code for all sheets. Observe the use of the CodeName to address the "Home" tab.

Private Sub CmdHome_Click()
    ' 215
    Dashboard.Activate
End Sub

When the Dashboard is activated the Activate event procedure in its code module will be triggered.

Private Sub Worksheet_Activate()
    ' 215

    Dim Ws      As Worksheet

    For Each Ws In ThisWorkbook.Worksheets
        If Not Ws Is Dashboard Then
            If Ws.Visible <> xlSheetVeryHidden Then Ws.Visible = xlSheetVeryHidden
        End If
    Next Ws
End Sub

This code loops through all sheets and hides them (except the Dashboard itself). This is where you can change xlSheetVeryHidden to xlSheetHidden if you want to allow the user to easily unhide sheets. I decided to hide all sheets at this point although only one should be visible. This is because the user might have made a sheet visible that isn't the one on which he clicked the "Home" button.

BTW, you might copy/paste the entire code module of the Dashboard to the appropriate code module in your own project.

Discuss

Discussion

Upvote for the first two phrases alone! :) Also, great answer!
don (rep: 1989) Apr 8, '21 at 7:57 am
Agreed! 
John_Ru (rep: 6102) Apr 8, '21 at 8:03 am
Add to Discussion
1

Armely

Here's an alternative approach, using normal hyperlinks in cells rather than buttons.

In the attached file, there's a sheet called "Homepage" and behind that is this event module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim CellLink As String, InWb As Integer

On Error Resume Next 'allow for targets without links

CellLink = Target.Hyperlinks(1).SubAddress 'get hyperlink address
InWb = InStr(CellLink, "!") 'see if ! in subaddress

If CellLink = "" Or InWb = 0 Then Exit Sub

Worksheets(Left(CellLink, InStr(CellLink, "!") - 1)).Visible = True 'unhide page
Target.Hyperlinks(1).Follow 'go to the link location

End Sub
When you click on a cell, if it has a hyperlink to another sheet then that sheet is made visible and link is followed. (You can change the hyperlink  text to something more meaningful e.g in cell A3 of my file).

Behind that sheet is another event module which hides all the other pages when Homepage is clicked (activated):

Private Sub Worksheet_Activate()
HideOtherSheets
End Sub
by calling this macro (in Module 1) which makes all sheets (apart from Homepage) VeryHidden and so not visible to the user:
Sub HideOtherSheets()

Dim AllSh As Workbook, sh As Worksheet

Set AllSh = ThisWorkbook

For Each sh In AllSh.Sheets
    If sh.Name <> "HomePage" Then sh.Visible = xlSheetVeryHidden ' hide all sheets except HomePage from user
Next sh

End Sub
Note that the same module is called when the workbook is opened (see under ThisWorkbook in VB Explorer) so only the HomePage is shown.

To use this for your sheets, use Move or copy in your workbook to make copies of your 14 sheets in this file. In Homepage run the sub UnHideOtherSheets in Module 2 then add the hyperlinks you need. Then, without selecting Homepage again, delete my sheets and reselect Homepage (you'll see that if you try to add an hyperlink, the sheets aren't visible unless you first run UnHideOtherSheets).

Hope this helps.

Discuss

Discussion

Upvote for offering an alternative and a great answer as well!)
don (rep: 1989) Apr 8, '21 at 7:57 am
Thanks Don! 
John_Ru (rep: 6102) Apr 8, '21 at 8:00 am
Add to Discussion
0

Hello Armely,

The previous two solutions by Variatus and John_Ru are very good and I would like to offer a third. My approach uses shapes with macros assigned on each sheet rather than hyperlinks. I have attached my sample file so you can see how it works.

All it takes is adding a shape to each sheet and assigning a macro to the shape. Clicking on each shape will make that sheet the active sheet and hide the previous so only one sheet is active/visible. In the code you can change xlHidden to xlVeryHidden if needed; and you can add some protect/unprotect code if/as needed for your needs.

Here is the code I used:

Note: the macro code references the sheet's code name not the sheet's Tab name which allows the macro to still work even if the tab names are changed.

Sub Tab_1()

' use a variable for the sheet name so if the sheet name changes the code is unaffected
Dim SheetToHide As String

' asign the name of the active sheet to the variable "SheetToHide"
SheetToHide = ActiveSheet.Name

' make the desired sheet visible and the active sheet
Sheet1.Visible = xlSheetVisible
Sheet1.Activate

' hide the previously active sheet
Sheets(SheetToHide).Visible = xlSheetHidden


End Sub
'

Sub Tab_2()

' use a variable for the sheet name so if the sheet name changes the code is unaffected
Dim SheetToHide As String

' asign the name of the active sheet to the variable "SheetToHide"
SheetToHide = ActiveSheet.Name

' make the desired sheet visible and the active sheet
Sheet2.Visible = xlSheetVisible
Sheet2.Activate

' hide the previously active sheet
Sheets(SheetToHide).Visible = xlSheetHidden

End Sub
'

Sub Tab_3()

' use a variable for the sheet name so if the sheet name changes the code is unaffected
Dim SheetToHide As String

' asign the name of the active sheet to the variable "SheetToHide"
SheetToHide = ActiveSheet.Name

' make the desired sheet visible and the active sheet
Sheet3.Visible = xlSheetVisible
Sheet3.Activate

' hide the previously active sheet
Sheets(SheetToHide).Visible = xlSheetHidden


End Sub

As you can see the only difference in each macro which makes adapting this code easy is at the line "make the desired sheet visible and the active sheet" 

Enjoy

Discuss

Discussion

Nice one, Willie! 
John_Ru (rep: 6102) Apr 11, '21 at 4:46 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login