Combine two Subs into One (Using One Button)

[tomexcel1]

Hi All

I currently have a worksheet with two command buttons by pressing one it hides all columns in a range that are blank, the other unhides all columns in that range. Is there a way to combine the two so i can use just one button "Hide/Unhide"?

This is the code i have to hide columns:

Dim ws As Worksheet Dim rng As Range
    
    Application.ScreenUpdating = False
    Sheet12.Unprotect ("passwordhere")
    Set ws = Sheet12
   
    For Each rng In ws.Range("D7:BB7")
        If rng.Value = "" Then
            rng.EntireColumn.Hidden = True
        End If
    Next rng
    Sheet12.Shapes("CommandButton1").Visible = False
    Sheet12.Shapes("CommandButton2").Visible = True
    Sheet12.Protect ("passwordhere")

This is the code i have to unhide columns:

Sheet12.Unprotect ("passwordhere")
Sheet12.Columns("D:BB").Hidden = False
Sheet12.Shapes("CommandButton1").Visible = True
Sheet12.Shapes("CommandButton2").Visible = False
Sheet12.Protect ("passwordhere")

Thanks in Advance
Tom

 

[Fluff]

Are these ActiveX or Forms Control buttons?

 

[tomexcel1]

ActiveX

Thanks
Tom

 

[Fluff]

Forgot to ask, is the button on the sheet you are hiding the columns?

 

[Fluff]

Never mind, try

Sub CommandButton1_Click()
   Dim Cl As Range
   [COLOR=#ff0000]Me[/COLOR].Unprotect "Password"
   With Me.CommandButton1
      If .Caption = "Hide" Then
         For Each Cl In [COLOR=#ff0000]Me[/COLOR].Range("D7:BB7")
            If Cl.Value = "" Then Cl.EntireColumn.Hidden = True
         Next Cl
         .Caption = "Unhide"
      Else
         [COLOR=#ff0000]Me[/COLOR].Range("D:BB").EntireColumn.Hidden = False
         .Caption = "Hide"
      End If
   End With
   [COLOR=#ff0000]Me[/COLOR].Protect "Password"
End Sub

If the button is on a different sheet change the red Me to Sheet12

 

[tomexcel1]

Thank you!! Works perfectly Just what I wanted!

 

[Fluff]

You're welcome & thanks for the feedback