Forum Discussion

akashpohal's avatar
akashpohal
Copper Contributor
Jul 06, 2023

need help with microsoft excel dashboard

i am working on excel, and i want to create a VBA for below i have following sheet names: 1. QA Dashboard 2. Emirates 3. TA 4. AMEX 5. Spotify 6. SA 7. McCamm 8. Honda 9. Energizer 10. Acc...
Excel
Formulas and Functions
Macros and VBA
akashpohal's avatar
akashpohal
Copper Contributor

NikolinoDE 

 

hello there, this VBA was helpful in hiding all the sheets
however, after the sheets were hidden, the hyperlink doesn't work anymore.
my excel is in .xlsm format as you mentioned
still no luck

 

Any further advise on how can i make this hyperlink work after hidding my other sheets.

NikolinoDE's avatar
NikolinoDE
Gold Contributor
Jul 10, 2023

akashpohal 

When you hide sheets in Excel, the hyperlinks to those sheets may no longer work because the hidden sheets are not visible. However, you can modify your VBA code to unhide the necessary sheet temporarily, follow the hyperlink, and then hide the sheet again. Here's an example of how you can maybe achieve this:

Sub FollowHyperlinkAndHideSheets()
    Dim HyperlinkSheet As Worksheet
    Dim TargetSheet As Worksheet

    ' Set the HyperlinkSheet to the sheet containing the hyperlink
    Set HyperlinkSheet = ThisWorkbook.Sheets("SheetWithHyperlink")

    ' Set the TargetSheet to the sheet you want to navigate to
    Set TargetSheet = ThisWorkbook.Sheets("TargetSheet")

    ' Unhide the TargetSheet temporarily
    TargetSheet.Visible = xlSheetVisible

    ' Follow the hyperlink on the HyperlinkSheet
    HyperlinkSheet.Hyperlinks(1).Follow

    ' Hide the TargetSheet again
    TargetSheet.Visible = xlSheetHidden
End Sub

 

In this code, you need to replace "SheetWithHyperlink" with the actual name of the sheet that contains the hyperlink, and "TargetSheet" with the name of the sheet you want to navigate to.

 

Here is how you can use this code:

 

Press Alt + F11 to open the VBA editor.

Insert a new module by clicking on "Insert" and selecting "Module."

Paste the above code into the module.

Modify the sheet names as mentioned above.

Close the VBA editor.

Run the macro FollowHyperlinkAndHideSheets.

This macro will temporarily unhide the target sheet, follow the hyperlink, and then hide the target sheet again. This way, the hyperlink will work even when the sheet is hidden.

 

Please note that if you have multiple hyperlinks on the same sheet, you may need to adjust the Hyperlinks(1) part to match the specific hyperlink you want to follow. The text, steps and code were created with the help of AI.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

  • akashpohal's avatar
    akashpohal
    Copper Contributor
    Jul 11, 2023

    NikolinoDE 

     

    hello there,
    the first code works perfectly fine, where we had to hide all the files.
    however the second piece of VBA code, I am not sure, if I did it correct
    I have no experience in VBA and doing it for the first time, and this is what I did

     

    1. since I have the first VBA in module 1, I created a new module(module 2).

    2. I then pasted your 2nd code in it,

    3. I then replaced, below entries from:

    ' Set the HyperlinkSheet to the sheet containing the hyperlink
    Set HyperlinkSheet = ThisWorkbook.Sheets("SheetWithHyperlink")

    ' Set the TargetSheet to the sheet you want to navigate to
    Set TargetSheet = ThisWorkbook.Sheets("TargetSheet")

    to 

    ' Set the HyperlinkSheet to the sheet containing the hyperlink
    Set HyperlinkSheet = ThisWorkbook.Sheets("Emirates", "TA", "AMEX", "Spotify", "SA", "McCamm", "Honda", "Energizer", "Accenture-EMEA-and-NA", "Accenture-APAC")

    ' Set the TargetSheet to the sheet you want to navigate to
    Set TargetSheet = ThisWorkbook.Sheets("Emirates", "TA", "AMEX", "Spotify", "SA", "McCamm", "Honda", "Energizer", "Accenture-EMEA-and-NA", "Accenture-APAC")

     

    I then ALT+Q and went to main sheet, and then run both the marcos, save it and tried to test out the hyperlink, but no luck

     

    • NikolinoDE's avatar
      NikolinoDE
      Gold Contributor
      Jul 11, 2023

      akashpohal 

      The second code snippet I provided was intended as an example for handling individual sheets with hyperlinks, rather than multiple sheets within a single line.

      Here's the approach for handling multiple sheets with hyperlinks  (untested):

      Sub FollowHyperlinksAndHideSheets()
    Dim HyperlinkSheet As Worksheet
    Dim TargetSheet As Worksheet
    Dim Hyperlink As Hyperlink

    ' Set the HyperlinkSheet to the sheet containing the hyperlinks
    Set HyperlinkSheet = ThisWorkbook.Sheets("SheetWithHyperlinks")

    ' Loop through each sheet to follow hyperlinks and hide the sheets
    For Each TargetSheet In ThisWorkbook.Sheets
        ' Skip the HyperlinkSheet itself
        If TargetSheet.Name <> HyperlinkSheet.Name Then
            ' Check if the TargetSheet has a hyperlink
            For Each Hyperlink In HyperlinkSheet.Hyperlinks
                If Hyperlink.SubAddress = "'" & TargetSheet.Name & "'!A1" Then
                    ' Unhide the TargetSheet temporarily
                    TargetSheet.Visible = xlSheetVisible
                    
                    ' Follow the hyperlink
                    Hyperlink.Follow
                    
                    ' Hide the TargetSheet again
                    TargetSheet.Visible = xlSheetHidden
                    
                    Exit For
                End If
            Next Hyperlink
        End If
    Next TargetSheet
End Sub

      Modify the following line to match the sheet name containing the hyperlinks:

      Set HyperlinkSheet = ThisWorkbook.Sheets("SheetWithHyperlinks")

      This macro will loop through each sheet in the workbook, check for hyperlinks in the specified "HyperlinkSheet," follow the hyperlinks, and then hide the corresponding sheets. Make sure the hyperlinks are set up correctly with the format 'SheetName'!A1.

      Please ensure that the names of your "HyperlinkSheet" and the sheet containing hyperlinks match the actual names in your workbook. The text, steps and code was created with the help of AI

      I hope this updated code resolves the issue you were facing with the hyperlinks.

      • akashpohal's avatar
        akashpohal
        Copper Contributor
        Jul 11, 2023
        Hello there,
        i really appreciate you helping me on this,
        i tried with this as well, still no luck

        https://we.tl/t-DeJfPht8if

        I have this file attached here via wetransfer, with the macro in them,
        other sheets are already hidden as per the Macros

        any advise here?

        Akash

Resources