Control Button Toggle

[RandyD123]

Right now I have 2 control buttons, one is to hide all sheets except for active one and the other is to unhide all sheets. I would like to make that just one button that changes text so it will do both. Not sure how to go about that. My code is below. Any help would be most appreciated. Thank you.

Sub Hide()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden
Next ws

End Sub
Sub UnHide()
Dim ws As Worksheet
Set AC = ThisWorkbook.ActiveSheet

  Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetVisible
Next ws

AC.Select

End Sub

 

[dmt32]

Hi,
try

Sub Hide()
    Dim ws As Worksheet
    Set ac = ThisWorkbook.ActiveSheet
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then ws.Visible = _
        IIf(ws.Visible, xlSheetHidden, xlSheetVisible)
    Next ws
    
    With ac.Buttons(1)
            .Caption = IIf(.Caption = "UnHide", "Hide", "UnHide")
    End With
    ac.Select

End Sub

assumes your button is a forms control button

Dave

 

[RandyD123]

Hi,
try

Sub Hide()
    Dim ws As Worksheet
    Set ac = ThisWorkbook.ActiveSheet
  
    Application.ScreenUpdating = False
  
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ActiveSheet.Name Then ws.Visible = _
        IIf(ws.Visible, xlSheetHidden, xlSheetVisible)
    Next ws
  
    With ac.Buttons(1)
            .Caption = IIf(.Caption = "UnHide", "Hide", "UnHide")
    End With
    ac.Select

End Sub

assumes your button is a forms control button

Dave

Weird, I get error 400. And when I click "hide" all works as it should, my active sheet is the only sheet left, but when I click "unhide" they all unhide and my active sheet hides....the one with the button(s)!!!

 

[dmt32]

curious - works ok for me

try this small change & see if any different

Sub Hide()
    Dim ws As Worksheet
    Set ac = ThisWorkbook.ActiveSheet
    
    Application.ScreenUpdating = False
    
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> ac.Name Then ws.Visible = _
        IIf(ws.Visible, xlSheetHidden, xlSheetVisible)
    Next ws
    
    With ac.Buttons(1)
            .Caption = IIf(.Caption = "UnHide", "Hide", "UnHide")
    End With
    
    With ac
        .Visible = True
        .Select
    End With

End Sub

Dave

 

[RandyD123]

Works perfectly!! Thank You

 

[dmt32]

Works perfectly!! Thank You

welcome glad suggestion resolved for you & appreciate your feedback

Dave