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. Accenture-EMEA-and-NA
11. Accenture-APAC

QA dashboard is the sheet, where i have created 10 blocks and each block is named against the other block 2-11 (eg, starting from Emirates, TA....Accenture-APAC.)
so the idea is QA dashboard is main sheet, and have 10 blocks that will be hyperlinked to the other 10 sheets. i have already linked them using hyperlink and souring it to following sheets.
now the linking is done perfectly, and i have also added another blocks to each page which will redirect to HOME page i.e. QA dashboard

THE PROBLEM here is:
now i want to hide the sheet 2-11
however after hiding it, my hyperlinking which was working before is not working any more since the files are hidden

Any solution, which will help me achive it but hidding the sheets.
I dont know VBA and have never done it, but can try if someone gives to step by step guidance

excel
Formulas and Functions
Macros and VBA

10 Replies

Sort By
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jul 07, 2023

    akashpohal 

    If you want to hide the sheets 2-11 while still maintaining the functionality of the hyperlinks, you can use a VBA macro to achieve this.

    Here is an example of how you can implement it:

    1. Open the Excel workbook and press ALT+F11 to open the Visual Basic Editor.
    2. Insert a new module by clicking on "Insert" and selecting "Module."
    3. In the module window, paste the following VBA code:

    Vba code (untested):

    Sub HideSheetsAndSetHyperlinks()
    Dim ws As Worksheet
    
    ' Array of sheet names to hide
    Dim hiddenSheets As Variant
    hiddenSheets = Array("Emirates", "TA", "AMEX", "Spotify", "SA", "McCamm", "Honda", "Energizer", "Accenture-EMEA-and-NA", "Accenture-APAC")
    
    ' Hide the specified sheets
    For Each ws In ThisWorkbook.Sheets
        If IsInArray(ws.Name, hiddenSheets) Then
            ws.Visible = xlSheetHidden
        End If
    Next ws
    
    ' Set the hyperlinks in the QA Dashboard sheet
    Dim dashboardSheet As Worksheet
    Set dashboardSheet = ThisWorkbook.Sheets("QA Dashboard")
    
    ' Update the address of the hyperlinks to use the sheet codenames
    Dim blockNumber As Integer
    For blockNumber = 2 To 11
        Dim hyperlinkCell As Range
        Set hyperlinkCell = dashboardSheet.Range("A" & blockNumber)
        hyperlinkCell.Hyperlinks.Add Anchor:=hyperlinkCell, Address:="", SubAddress:="'" & Sheets(blockNumber).CodeName & "'!A1", TextToDisplay:=hyperlinkCell.Value
    Next blockNumber
End Sub

Function IsInArray(value As Variant, arr As Variant) As Boolean
    IsInArray = (UBound(Filter(arr, value)) > -1)
End Function

     

    1. Modify the hiddenSheets array in the code to include the names of the sheets you want to hide.
    2. Close the Visual Basic Editor.
    3. Press ALT+F8 to open the "Macro" dialog box.
    4. Select the "HideSheetsAndSetHyperlinks" macro and click "Run" to execute the code.

    This VBA macro will hide the specified sheets while preserving the functionality of the hyperlinks in the "QA Dashboard" sheet. It also updates the hyperlinks to use the sheet codenames instead of the sheet names, ensuring that the hyperlinks will still work even if the sheet names are hidden.

    Remember to save your Excel workbook in a macro-enabled format (e.g., .xlsm) to retain the VBA code. The text, steps and functions 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 10, 2023

      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.

Resources