Forum Discussion

Doc441

Copper Contributor

Apr 03, 2020

Can you create a checkbox that will hide or show a tab in a worksheet?

If I create a yes/no button could it trigger the hiding or exposing of an additional tab in the same worksheet?

Formulas and Functions

Riny_van_Eekelen

Platinum Contributor

Apr 03, 2020

Doc441 You need a small piece of VBA code for that. Create your checkbox, right-click it and select "Format Control..." and select the Cell Link (for example A1) and OK. Right-click again and "Assign Macro...". Now you select the macro that does the hiding and unhiding. Example below:

Sub Macro1()
Dim HideSheet As Boolean
HideSheet = Range("A1")

If HideSheet Then
    Sheets("Sheet1").Visible = False   
    Else
    Sheets("Sheet1").Visible = True
    End If
    
End Sub

This code assumes you have a check box linked to A1. It will hide "Sheet1" when ticked, and unhide it when not ticked. The attached workbook contains a working example.

hide_sheet.xlsm18 KB

Doc441

Copper Contributor

Apr 03, 2020

Riny_van_Eekelen Thanks for the reply....I would call myself an intermediate user but the coding seems to be an issue.

I am unsure of the HideSheet = Range("A1") component....

Thats the line that seems to be erroring.

I appreciate your guidance

Riny_van_Eekelen
Platinum Contributor
Apr 04, 2020
Doc441 Can't really tell. It works fine in my file. What error message do you get?
- Doc441
  Copper Contributor
  Apr 17, 2020
  I guess I just need the excel for dummies step by step version....
  
  I don't understand the HideSheet = Range("A1") line. In particular cell A1 on what page?.
  - Riny_van_Eekelen
    Platinum Contributor
    Apr 17, 2020
    Doc441
    
    The variable "HideSheet" is declared as a "Boolean". That means, it can hold TRUE or FALSE.
    
    A1 is the cell that is linked to the CheckBox. Check the box and A1 becomes TRUE. Uncheck the box and A1 becomes FALSE.
    
    The code "If HideSheet Then" looks if the box was checked. If so, Hide the sheet ELSE unhide it.