Show or hide a worksheet using a single button

3.8k views Asked by At

I have a user form with a single button.

Question: Is it possible to show or hide a worksheet using only a single button? If so what the code will look like?

I tried this one so far:

Private Sub data_Button_click()

If data_Button.Caption = "Hidden" Then
Worksheets("u").Visible = True
data_Button.Caption = "Visible"
End If

If data_Button.Caption = "Visible" Then
Worksheets("u").Visible = False
data_Button.Caption = "Hidden"
End If

End Sub
2

There are 2 answers

0
ZygD On BEST ANSWER

Merge those 2 If statements into one like this:

Private Sub data_Button_click()

If data_Button.Caption = "Hidden" Then
    Worksheets("u").Visible = True
    data_Button.Caption = "Visible"
ElseIf data_Button.Caption = "Visible" Then
    Worksheets("u").Visible = False
    data_Button.Caption = "Hidden"
End If

End Sub
2
omegastripes On

Try this:

Private Sub data_Button_Click()
    With Worksheets("u")
        .Visible = Not .Visible
        data_Button.Caption = IIf(.Visible, "Hide", "Show")
    End With
End Sub