Using VBA to Hide and Unhide Columns with Click of Button

[kskapin]

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.

 

[Trevor G]

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]

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.

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.

 

[kskapin]

HalfAce, that's exactly what I needed! Thank you very much!

 

[HalfAce]

Most welcome. Glad it helped.

 

[Rahulwork]

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]

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.

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...

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]

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.

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.)

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]

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.

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.)

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]

Here are two examples:

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