Forum Discussion
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...
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
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 SubModify 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.
- 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?
AkashSry 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.
