Please Help - Hiding/Unhiding code - MACRO/VBA

Hi All,

I have no background nor proper training on handling VBA/Macros but sooner I would like to learn at least the basic of it. I am thinking to create a template that would help me navigating on my report efficiently, can anyone could please help me create a simple macro that control (hid/unhide) tabs on my excel – then probably I can use it to pattern on others.

Ideas:
Control Tab – this contains shapes which I would like to assign a macro when clicked. They are Week 1, 2, 3, 4, Hide All, Dashboard, Report and Tally.

The idea is to show ONLY the tabs that I would like to see on my workbook.

Expected Outcomes:
1. When I clicked WEEK 1, it will only show tabs W1-1, W1-2, W1-3, W1-4 and W1-5
2. When I clicked WEEK 2, it will only show tabs W2-1, W2-2, W2-3, W2-4 and W2-5
3. When I clicked WEEK 3, it will only show tabs W3-1, W3-2, W3-3, W3-4 and W3-5
4. When I clicked WEEK 4, it will only show tabs W4-1, W4-2, W4-3, W4-4 and W4-5
5. When I clicked HIDE ALL, it will hide all tabs EXCEPT the CONTROL tab
6. When I clicked DASHBOARD, it will only show the DASHBOARD and the CONTROL tabs
7. When I clicked REPORT, it will only show the REPORT and the CONTROL tabs
8. When I clicked TALLY, it will only show the TALLY and the CONTROL tabs

By the way, I am using excel 2016

Kindly see the sample workbook I’ve attached for your reference.

Thank you all in advance…I would take it as a first step to a more complicated VBA functions.

Appreciate your help.

Dy

I forgot, on the Expected Outcomes 1-4, it should also show the CONTROL tab and the corresponding weeks' tabs.

Hi dy137,

you can try this.

Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name <> "Control" And sh.Name <> "W1-1" And sh.Name <> "W1-2" And sh.Name <> "W1-3" And sh.Name <> "W1-4" And sh.Name <> "W1-5" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh

You should place that under the Wk1 button, then just modify it with the other buttons.

Originally Posted by aarona

Hi dy137,

you can try this.

Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name <> "Control" And sh.Name <> "W1-1" And sh.Name <> "W1-2" And sh.Name <> "W1-3" And sh.Name <> "W1-4" And sh.Name <> "W1-5" Then
            sh.Visible = xlSheetVeryHidden
        End If
    Next sh

You should place that under the Wk1 button, then just modify it with the other buttons.

Hi aarona,

Thanks for your help ..it is a good start for me...a little bit of favor...can you also create one for HIDE ALL - from there, I can already start playing around with the code.. Thanks

Sure, here

Dim sh As Worksheet

    For Each sh In Sheets
    
        If sh.Name <> "Control" Then
            sh.Visible = xlSheetHidden
        End If
    Next sh

Hello MarvinP,

This is great! you've made this easy for me..

I have a concern though, when I clicked Week 1 for example, I am already lost - I cannot go back to the control tab...apologies, I actually have added this requirement on my second reply...

Here's how it should work, When WEEK 1,2,3 or 4 is clicked, it should show its corresponding tabs plus the CONTROL tab...this should also apply to all macro

I could probably look at how you did it in other tab and add it...I may be asking too much

Thanks MarvinP! Appreciate it

Originally Posted by MarvinP

You should add the Dashboard sheet when you click on the individual weeks so you can then go to the other subs. BUT - you didn't ask for that.

Something I realized. You're right! That should also be part of it..

Can we make the CONTROL tab available for ALL then? - I'm not sure If I can do this without that much knowledge in VBA but I will try to pattern with your work.. :/