• 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

Chihiro

Excel Ninja
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

Chihiro

Excel Ninja
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

"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.
 

Chihiro

Excel Ninja
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.
 

rararara

New Member
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

Top