need help with microsoft excel dashboard

[akashpohal]

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

[#2]

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

[#3]

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

[#4]

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

[#5]

@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

[#6]

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

[#7]

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

[#8]

Hello @NikolinoDE

just wanted to check if you help me further on this please
akash

[#9]

@akashpohal 

Sry but since I don't have much time at the moment, I'll send a simple and quick example to Emirates. You can convert the other buttons as well, just need to delete the hyperlinks.

[#10]

Het thanks a lot
this solved my issue 🙂

[#11]

Sorry I'm late to this conversation but wondering if you might be able to use a NON macro solution, which would also work for using Excel Online. Depending on what is on and what you need to do in those other tabs. For example if it is just information and comparison values (i.e. not data entry) then you could instead just use the QA tab as the reporting tab that will display the information from the other tabs. Instead of links I would use a drop down (Data Validation) and then a formula to 'pull' the data from those other tabs to show the user the data from that tab on the QA tab. If this type of solution might work for you I would be happy to help you do that.