Using VBA to Hide and Unhide Columns with Click of Button

kskapin

New Member
Joined
Mar 14, 2012
Messages
15
Hello!

I would like a button to be pressed that would hide specific columns. Once the button is pressed again, the columns appear. I'm extremely new to VBA, so I'm not sure of code syntax just yet.

Private Sub CommandButton1_Click()
Columns("D:G").Hidden , Columns("AF:AG").Hidden, Columns("AJ:AO").Hidden = Not Columns("A:AP").Hidden
End Sub

This is what I was attempting to use. I would like the indicated columns (D:G, AF:AG, AJ:AO) to hide upon pressing the button, then all of the columns to show again once pressed again. Ideally, the button could also be labeled to show "Hide Information" and "Show Information" correctly.

Thank you so much for all your help. :biggrin:
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
If you use a forms button you can assign it to a macro and the code for the macro would be to hide the columns

Sub Macro1()
Range("D:G,AF:AG,AJ:AO").EntireColumn.Hidden = True
End Sub

Another button to the second macro would be

Sub Macro2()
Cells.EntireColumn.Hidden = False
End Sub
 
Upvote 0
If you're actually using CommandButton1 (as in your example) you can go to the properties for that button and give it the caption "Hide Information". Make sure your columns of interest are showing and then use this code for the button.
Code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Information" Then
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = True
  CommandButton1.Caption = "Show Information"
Else
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = False
  CommandButton1.Caption = "Hide Information"
End If
End Sub

You can also use a Forms button the same way. Let us know if that's what you really want to do.

Hope it helps.
 
Upvote 0
hey friend i need your help for small problem, i want to hide and open column and rows but i want when button pressed a userform or we can say a password popup should appear for password, and after enter the correct password we can able to unhide the coulmn and rows or we can say given range in coding
 
Upvote 0
If you're actually using CommandButton1 (as in your example) you can go to the properties for that button and give it the caption "Hide Information". Make sure your columns of interest are showing and then use this code for the button.
Code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Information" Then
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = True
  CommandButton1.Caption = "Show Information"
Else
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = False
  CommandButton1.Caption = "Hide Information"
End If
End Sub
Another way to write your code...
Code:
Private Sub CommandButton1_Click()
  With Range("D:G, AF:AG, AJ:AO")
    .EntireColumn.Hidden = Not .EntireColumn.Hidden
    CommandButton1.Caption = Choose(.EntireColumn.Hidden + 2, "Show ", "Hide ") & "information"
  End With
End Sub
 
Upvote 0
Rahulwork,
Assuming you only want the password to come up when the user is wanting to show the hidden columns (and not when they want to hide them) then I would do something like this.
Code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Information" Then
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = True
  CommandButton1.Caption = "Show Information"
Else
  ShowPword = InputBox("Enter password to show hidden data")
  If ShowPword = "" Then Exit Sub
  If ShowPword <> "ok" Then MsgBox "Incorrect password...": Exit Sub  'Replace 'ok' with your real password
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = False
  CommandButton1.Caption = "Hide Information"
End If
End Sub

However... if you want the password to be required for showing and hiding then I would use Rick's code with the addition of the inputbox. (Just because I think it's more cool.)
Code:
Private Sub CommandButton1_Click()
  ShowPword = InputBox("Enter password to show hidden data")
  If ShowPword = "" Then Exit Sub
  If ShowPword <> "ok" Then MsgBox "Incorrect password...": Exit Sub 'Replace 'ok' with your real password
  With Range("D:G, AF:AG, AJ:AO")
    .EntireColumn.Hidden = Not .EntireColumn.Hidden
    CommandButton1.Caption = Choose(.EntireColumn.Hidden + 2, "Show ", "Hide ") & "information"
  End With
End Sub

Sorry it's taken so long. I don't get to be here as often as I used to.

Hope it helps.
 
Upvote 0
Rahulwork,
Assuming you only want the password to come up when the user is wanting to show the hidden columns (and not when they want to hide them) then I would do something like this.
Code:
Private Sub CommandButton1_Click()
If CommandButton1.Caption = "Hide Information" Then
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = True
  CommandButton1.Caption = "Show Information"
Else
  ShowPword = InputBox("Enter password to show hidden data")
  If ShowPword = "" Then Exit Sub
  If ShowPword <> "ok" Then MsgBox "Incorrect password...": Exit Sub  'Replace 'ok' with your real password
  Range("D:G, AF:AG, AJ:AO").EntireColumn.Hidden = False
  CommandButton1.Caption = "Hide Information"
End If
End Sub

However... if you want the password to be required for showing and hiding then I would use Rick's code with the addition of the inputbox. (Just because I think it's more cool.)
Code:
Private Sub CommandButton1_Click()
  ShowPword = InputBox("Enter password to show hidden data")
  If ShowPword = "" Then Exit Sub
  If ShowPword <> "ok" Then MsgBox "Incorrect password...": Exit Sub 'Replace 'ok' with your real password
  With Range("D:G, AF:AG, AJ:AO")
    .EntireColumn.Hidden = Not .EntireColumn.Hidden
    CommandButton1.Caption = Choose(.EntireColumn.Hidden + 2, "Show ", "Hide ") & "information"
  End With
End Sub

Sorry it's taken so long. I don't get to be here as often as I used to.

Hope it helps.

Hello experts,

I am facing a similar problem but I need to hide every alternate columns - that is, Column A,C,E,G..so and so forth. I might even add a few more columns to the right in the future. How can I count alternate columns?
 
Upvote 0
Here are two examples:

Code:
Private Sub CommandButton1_Click() ' working with a lot of columns
Dim spw, i%
spw = InputBox("Enter password to show hidden data")
If spw = "" Then Exit Sub
If spw <> "ok" Then MsgBox "Incorrect password...": Exit Sub
For i = 1 To 20 Step 2
    With Columns(i)
      .Hidden = Not .Hidden
      CommandButton1.Caption = Choose(.Hidden + 2, "Show ", "Hide ") & "information"
    End With
Next
End Sub


Private Sub CommandButton12_Click() ' working with only a few columns
Dim spw
spw = InputBox("Enter password to show hidden data")
If spw = "" Then Exit Sub
If spw <> "ok" Then MsgBox "Incorrect password...": Exit Sub
With Range("a:a,c:c,e:e,g:g,i:i")
  .EntireColumn.Hidden = Not .EntireColumn.Hidden
  CommandButton1.Caption = Choose(.EntireColumn.Hidden + 2, "Show ", "Hide ") & "information"
End With
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
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