Using multiple checkboxes to hide and show specific columns Excel 2010

[selesz]

Hi,

I would be very grateful for some help.
Basically I have a big table containing several columns for the different quarters. (Each quarter has several columns which are not all next to each other)
I would like to use 4 checkboxes which the user can select the show or hide the columns for that quarter. E.g. if Q1 and Q3 are checked only those are visible)
(if quarter 1 clicked than all columns are visible if unclicked hidden).
The code I have written workes well for the first checkbox (Q1), but for the other quarters (Q2, Q3 and Q4) the columns get hidden when checked (which should be the other way round) and don't get unhidden when unchecked.

Sub CheckboxQone()
If Range("$A$1").Value = True Then
Call showQ1
Else: Range("$A$1").Value = False
Call hideQ1
End If
End Sub
Sub showQ1()
Range("D:D,L:O,AD:AE,AN:AO,AX:AY,BK:BL,BW:BW,CC:CC").EntireColumn.Hidden = False
End Sub
Sub hideQ1()
Range("D:D,L:O,AD:AE,AN:AO,AX:AY,BK:BL,BW:BW,CC:CC").EntireColumn.Hidden = True
End Sub
Sub CheckboxQtwo()
If Range("$A$2").Value = True Then
Call showQ2
Else: Range("$A$2").Value = False
Call hideQ2
End If
End Sub
Sub showQ2()
Range("E:E,P:S,AF:AG,AP:AQ,AZ:BA,BM:BN,BX:BX,CD:CE").EntireColumn.Hidden = False
End Sub
Sub hideQ2()
Range("E:E,P:S,AF:AG,AP:AQ,AZ:BA,BM:BN,BX:BX,CD:CE").EntireColumn.Hidden = True
End Sub

Sub CheckboxQthree()
If Range("$A$3").Value = True Then
Call showQ3
Else: Range("$A$3").Value = False
Call hideQ3
End If
End Sub
Sub showQ3()
Range("F:F,T:W,AH:AI,AR:AS,BB:BC,BO:BP,BY:BY,CF:CG").EntireColumn.Hidden = False
End Sub
Sub hideQ3()
Range("F:F,T:W,AH:AI,AR:AS,BB:BC,BO:BP,BY:BY,CF:CG").EntireColumn.Hidden = True
End Sub
Sub CheckboxQfour()
If Range("$A$4").Value = True Then
Call showQ4
Else: Range("$A$4").Value = False
Call hideQ4
End If
End Sub
Sub showQ4()
Range("G:G,X:AA,AJ:AK,AT:AU,BD:BE,BQ:BR,BZ:BZ,CH:CI").EntireColumn.Hidden = False
End Sub
Sub hideQ4()
Range("G:G,X:AA,AJ:AK,AT:AU,BD:BE,BQ:BR,BZ:BZ,CH:CI").EntireColumn.Hidden = True
End Sub



I would really appreciate your help with this one!

Many thanks in advance

Eszter

 

[Gary McMaster]

Hi Eszter,

Welcome to the board.

If you use "ActiveX" check boxes the code can be a one liner in the check box "click event". You can use, or not use, the "NOT" keyword to invert the action of a check or uncheck as shown below.

Hope that helps.

Gary

Private Sub CheckBox1_Click()

ActiveSheet.Range("A:A,C:C,E:E").EntireColumn.Hidden = Not CheckBox1.Value

End Sub

Private Sub CheckBox2_Click()

ActiveSheet.Range("B:B,D:D").EntireColumn.Hidden = Not CheckBox2.Value

End Sub

 

[selesz]

Hi Eszter,

Welcome to the board.

If you use "ActiveX" check boxes the code can be a one liner in the check box "click event". You can use, or not use, the "NOT" keyword to invert the action of a check or uncheck as shown below.

Hope that helps.

Gary

Private Sub CheckBox1_Click()

ActiveSheet.Range("A:A,C:C,E:E").EntireColumn.Hidden = Not CheckBox1.Value

End Sub

Private Sub CheckBox2_Click()

ActiveSheet.Range("B:B,D:D").EntireColumn.Hidden = Not CheckBox2.Value

End Sub

Hi Gary,

Thank you so very much! It worked fantastically.
I really appreciate your quick help.

Many thanks
Eszter