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:
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,362
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Most welcome. Glad it helped.
 

Rahulwork

Board Regular
Joined
Jun 9, 2013
Messages
244
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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,511
Office Version
2010
Platform
Windows
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
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
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.
 

pretzel

New Member
Joined
Oct 2, 2014
Messages
18
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?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,803
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
 

Forum statistics

Threads
1,082,610
Messages
5,366,601
Members
400,906
Latest member
incanus

Some videos you may like

This Week's Hot Topics

Top