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

hyperlink to hidden sheet and hide again when hyperlinking back

0

Hi Guys ,

I need your help i have about 10 sheet on excel 2016 i want the first sheet to be a dashboard and the rest hidden the when they press on a hyperlink then it views the hidden sheet.

the secont thing is on the hidden sheet i want to hyperlink back then it hides the sheet again.

Need VBA code please help me :) 

excelmacros-vba
MS Office Version: 2016
posted by: Kay (rep: 2) on Wed Nov 15, 2017 at 3:46 am
Answer
Discuss

Answers

0
Selected Answer

Here is a very simple and easy-to-understand solution:

Sub GoToSheet2()

Sheets("Sheet2").Visible = True

Sheets("Sheet2").Activate

End Sub
Sub HideSheet2()

Sheets("Sheet1").Activate

Sheets("Sheet2").Visible = False

End Sub

You can copy these two macros for each sheet that you want to appear and disappear.

Now, this isn't the smallest or most elegant solution, but it is the easiest to maintain for novice macro users.

Updated

Added example file to the answer.

posted by: don (rep: 1989) on Wed Nov 15, 2017 at 3:59 am
Discuss

Discussion

Hi Don,

do u maybe have skype for business ? so i can show you what im talking about?
i tried this but it did not work
Kay (rep: 2) Nov 15, '17 at 4:52 am
If you would like paid consulting services, through which you can communicate via Skype, you need to hit the Contact button at the top of the page and msg there.

For the above macro, change everywhere it says Sheet1 or Sheet2 to the name of the worksheets in your workbook. I assumed Sheet1 would be the dashboard sheet. Otherwise, there shouldn't be an issue.
don (rep: 1989) Nov 15, '17 at 5:10 am
You must connect the macros to buttons on your sheets.. On the 'Dashboard' you should have a button to view 'Sheet2' (you can change the name) and that button must run the macro `GoToSheet2' when pressed.
Similarly, on the target sheet ("Sheet2" or any other name) you should have a button "Return to Dashboard" which runs the macro 'HideSheet2' when clicked. If your dashboard works differently, please post a copy of it. You can attach it to your original question.

BTW, the worksheets' Visible property has 3 possible long values, xlSheetVisible, xlSheetHidden and xlSheetVeryHidden which have the values -1, 0 and 2. Setting the property to TRUE will set it to -1 (=xlSheetVisible) and False evaluates to 0 (=xlSheetHidden). If you want the sheets "Very hidden" use the integer 2 or the enumeration 'xlSheetVeryHidden' instead of "False" in Don's code.
The difference between "Hidden" and "Very hidden" is that the user can simply unhide any hidden sheet using they keyboard (Format -> Hide/Unhide). "Very hidden" sheets can't be made visible in that way. To make very hidden sheets visible the 'Visible' property must be set, either in the VBA project or using VBA code.
Variatus (rep: 4889) Nov 15, '17 at 5:25 am
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)  On Error GoTo FixThings   Dim strAddress As String     strAddress = _   Application.Substitute(Target.SubAddress, "'", vbNullString)   ThisWorkbook.Worksheets(Left$(strAddress, _   InStr(1, strAddress, "!") - 1)).Visible = xlSheetVisible   Application.EnableEvents = False   Target.Follow FixThings:   Application.EnableEvents = true   End Sub


i was trying to use this code
Kay (rep: 2) Nov 15, '17 at 6:42 am
@Variatus i used veryhidden then the hyperlink does not work 
Kay (rep: 2) Nov 15, '17 at 6:43 am
@don i did change the sheet names 
Kay (rep: 2) Nov 15, '17 at 6:44 am
Kay, start with a fresh new workbook and remove that hyperlink code you have. Start blank, insert the macros that I gave you and it should be very easy to get them to run. I will upload a sample workbook to my Answer.
don (rep: 1989) Nov 15, '17 at 6:48 am
Add to Discussion


Answer the Question

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