Hide/Unhide Worksheets using button form control

[crenson313]

Hello ! Chandoo :3


It's my first time posting a question in this forum , i'm new here , I' don't know that much about excel , I've been using excel as part of my daily life as encoder , You see i have this problem , i have this workbook named "FORMAT" and inside that , you will find a lot of data, sheets and every sheets there's a formula corresponding to them ,

so my problem is , I want to hide the sheets named " BCD all the way to ZAM " except the Main sheet , violet , blue , red and yellow sheets ,

In my main sheet i want to put a button each button will hide and unhide the sheet named BCD - all the way to ZAM ,

BCD up to ZAM are locations named as BCD- Bacolod , ZAM - Zamboanga

For example , the only open sheets are the main , violet ,blue red & yellow the rest are hide.

in the main there's a button each button will open the corresponding location sheet.

i want to open the "BCD " sheet , well you need to click the button named "BCD"

to show up ,

i want to show the BSO , then i will click the button named " BSO " to show up again,

the rest will do the same to following locations with each different buttons,

and finally i can also hide it again after i click the same button.


Below is my example workbook, i used this format everyday , but , there's alot of sheets expose so i decided to have a main sheet so that not all my desired sheets are shown , please feel free to explore it and please tweak it for me and please let me see your work and send it back to me, please share your awesomeness and let me be your apprentice :3



Thank you very much :3
-Rens Philippines

 

[Asheesh]

Welcome to the forum

Option Compare Text

Private Sub btnHide_unHide_Click()

Dim sh As Integer
Dim myBln As Boolean

With Application
    
    .ScreenUpdating = False
    
    .DisplayAlerts = False

End With

        If btnHide_unHide.Caption = "Hide" Then
            
            btnHide_unHide.Caption = "Unhide"
            
            myBln = 0
        
        Else
            
            btnHide_unHide.Caption = "Hide"
            
            myBln = 1
            
        End If
        

    For sh = 1 To Sheets.Count
    
   'change August as per your requirement
   
        If Sheets(sh).Name = "Main" Or Sheets(sh).Name = "Offload" Or Sheets(sh).Name Like "*August*" Then
         
            GoTo nextsheet
         
        Else
        
            Sheets(sh).Visible = myBln
        
        End If

nextsheet:
    Next sh
    
With Application
    
    .ScreenUpdating = True
    
    .DisplayAlerts = True

End With
    
End Sub

To unhide a particular tab, i shall share the code in a while...as I am going for a meeting right now..

 

[crenson313]

Wow !!! Ohh my Gosh ! Thank you Master Asheesh ! Awesome !

I didn't expect to get a reply this fast , this is really i wanted to have in my excel , The code is fantastic , though i didn't understand a lot of this things , i am now really hooked up to learn more things in excel in this org , I wonder what will it be look like in "unhide particular tab" , :3

 

[NARAYANK991]

Hi ,

Here is one more way.

Narayan

 

[crenson313]

Wow ! thank you Master Narayan great idea instead of button you chose checkbox its really nice , this will be great if i combine your work and Master Asheesh worked, but how will i do that , can you guide me pls ? :3 Thank you Masters

 

[Asheesh]

Hi,

Just combined the 2 together and havent changed anything...

 

[crenson313]

Wow ! i never thought it was so easy to combine together, really helpful Masters , Last Question Masters :3


What if i would like to add another checkbox for the two yellow sheets " august 1-15" & "August 16-31", do i have to change or add thru this code ? also does the changes of codes will debug the worked of master sheesh ? :3 Which of this should i change ?

Sub Hide_Unhide_Tabs()
    Const HIDE_TAB = "Button 1"
    Const UNHIDE_TAB = "Button 2"
   
    Const FIRST_CHECKBOX_NUMBER = 3
    Const LAST_CHECKBOX_NUMBER = 22
   
    Const CHECKED = 1
    Const UNCHECKED = -4146
   
    Dim button_name As String
    button_name = Application.Caller
   
    Application.ScreenUpdating = False
   
    For i = FIRST_CHECKBOX_NUMBER To LAST_CHECKBOX_NUMBER
        If ActiveSheet.Shapes("Check Box " & i).ControlFormat.Value = CHECKED Then
           cellval = ActiveSheet.Shapes("Check Box " & i).TopLeftCell.Address
           If button_name = HIDE_TAB Then
              Worksheets(Range(cellval).Offset(, -2).Value).Visible = xlSheetHidden
           Else
              Worksheets(Range(cellval).Offset(, 1).Value).Visible = xlSheetVisible
           End If
           ActiveSheet.Shapes("Check Box " & i).ControlFormat.Value = UNCHECKED
        End If
    Next
   
    Application.ScreenUpdating = True
End Sub