Combine two Subs into One (Using One Button)

tomexcel1

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

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Are these ActiveX or Forms Control buttons?
 
Upvote 0
Forgot to ask, is the button on the sheet you are hiding the columns?
 
Last edited:
Upvote 0
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
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top