• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Hyperlink to hidden sheets

Hi i have a workbook withv 10 sheets.
sheet 1 should be the index sheet containing hyperlinks to all the other 9 sheets.
sheet 1 is unhidden and all other 9 sheets are hidden.
When i click on the hyperlink to sheet 2 in index sheet, it should open the sheet2 and when i click on back icon on sheet 2, it should hide sheet 2 and go back to index sheet.
similarly i should do for all the other sheets.
how can i do it with excel vba macros. can anyone guide me step by step. i am new to macros vba.
 

Attachments

  • Capture.JPG
    Capture.JPG
    94.1 KB · Views: 33
Hi, welcome to the forum.

It would help if you upload a sample workbook with your set up.

There are many ways to do this. Two that immediately comes to mind are...
1. Using Workbook_SheetFollowHyperlink event. Intercept regular hyperlink click, and change it's behaviour by first unhiding the sheet.

2. Instead of using Hyperlink, use command button to navigate to said sheet.

If you need further guidance, upload sample workbook. Not just the picture.
 
Hi, welcome to the forum.

It would help if you upload a sample workbook with your set up.

There are many ways to do this. Two that immediately comes to mind are...
1. Using Workbook_SheetFollowHyperlink event. Intercept regular hyperlink click, and change it's behaviour by first unhiding the sheet.

2. Instead of using Hyperlink, use command button to navigate to said sheet.

If you need further guidance, upload sample workbook. Not just the picture.
 

Attachments

  • Auditing Tool_New_Macro 2.xlsm
    454.2 KB · Views: 51
Ok, so hyperlinks are embedded in shape object.

In that instance, 1st method won't work as it will not be able to capture the FollowHyperlink event within object.

I'd do it like below.

1. Remove hyperlinks from each shape in "Home_Navigation" sheet
2. Rename each shape to match corresponding worksheet (exact match).

Then you can add code below to your project in standard module. And assign it to all of the shapes in home sheet.

Code:
Sub demo()
    Dim shp As Shape

    Set shp = ActiveSheet.Shapes(Application.Caller)
    With Worksheets(shp.Name)
        .Visible = True
        .Select
    End With
End Sub
 
Hi Chihiro, i am using macros vba for first time and i didn't understand the steps which you said. Can you please Make changes in the excel sheet which i sent and can you send it to me. I am struck very badly

Home_Navigation is my Index Sheet
Home_ Navigation and Cover Page need not be hidden.
Remaining pages need to be hidden.
 

Attachments

  • Auditing Tool_New_Macro 2.xlsm
    451.1 KB · Views: 37
"Refer finding analysis" is still to be added and i am working on it. So please ignore "Refer Finding Analysis". i will add hyperlink for it "Refer finding analysis" later on.
 
Curiously I did something very similar today for someone else here: https://www.excelguru.ca/forums/showthread.php?8704
Attached is something very similar to Chihiro's solution but instead of using the shape's name I used its .AlternativeText where I've manually entered the name of the sheet that should be activated:
upload_2018-1-4_14-32-10.png

upload_2018-1-4_14-33-48.png
Also a slightly different way to hide the sheets again.
 

Attachments

  • Chandoo36863Auditing Tool_New_Macro 2.xlsm
    455.6 KB · Views: 68
Select each shape and right click. It will give shape name at top left.
upload_2018-1-4_9-56-10.png

Click and type in the sheet name. Do it for all of the shapes.

Then go into each VB editor (VBE) and add a module, paste in my code (or you can just add the code to one of existing module).

Then create new shape with hyperlink back to Home_Navigation sheet. Copy and paste that shape into each of target sheet.

Back in VBE double click on Home_Navigation sheet object and add following code.
Code:
Private Sub Worksheet_Activate()
    Dim ws As Worksheet
   
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Home_Navigation" And ws.Name <> "Cover Page" Then
            ws.Visible = False
        End If
    Next
 
End Sub

Then finally, right click on each shape and assign the macro.
 
hi, i have the same problem cause im adding sheets everyday. so what i need is to open 2 connected sheets when i click the hyperlinks on the "INDEX" sheet. kindly help me out please.

>update the INDEX sheet if i add additional sheet
 

Attachments

  • FILE1.xlsx
    11 KB · Views: 33
Select each shape and right click. It will give shape name at top left.
View attachment 48581

Click and type in the sheet name. Do it for all of the shapes.

Then go into each VB editor (VBE) and add a module, paste in my code (or you can just add the code to one of existing module).

Then create new shape with hyperlink back to Home_Navigation sheet. Copy and paste that shape into each of target sheet.

Back in VBE double click on Home_Navigation sheet object and add following code.
Code:
Private Sub Worksheet_Activate()
    Dim ws As Worksheet
  
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Home_Navigation" And ws.Name <> "Cover Page" Then
            ws.Visible = False
        End If
    Next

End Sub

Then finally, right click on each shape and assign the macro.


Hello, I am having the same issue. I am trying to insert a hyperlink in a shape that sends the user to a hidden sheet. name of hidden sheet does not match hyperlink text. can you please help?

Hidden sheet: DASHBOARD
Hyperlink text: Go to dashboard

Thank you in advance!
 
Back
Top