Combine two Subs into One (Using One Button)

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
40
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:

Code:
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:

Code:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,759
Office Version
365
Platform
Windows
Are these ActiveX or Forms Control buttons?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,759
Office Version
365
Platform
Windows
Forgot to ask, is the button on the sheet you are hiding the columns?
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,759
Office Version
365
Platform
Windows
Never mind, try
Code:
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
42,759
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,102,508
Messages
5,487,291
Members
407,590
Latest member
Grobler

This Week's Hot Topics

Top