PDA

View Full Version : Using multiple checkboxes to hide and show specific columns Excel 2010



selesz
05-12-2014, 03:21 AM
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

Bob Phillips
05-12-2014, 04:01 AM
See if this works better


Sub CheckboxQone()
Call showHideQuarters(1, Range("$A$1").Value)
End Sub

Sub CheckboxQtwo()
Call showHideQuarters(2, Range("$A$2").Value)
End Sub

Sub CheckboxQthree()
Call showHideQuarters(3, Range("$A$3").Value)
End Sub

Sub CheckboxQfour()
Call showHideQuarters(4, Range("$A$4").Value)
End Sub

Sub showHideQuarters(ByVal Quarter As Long, ByVal ShowColumns As Boolean)
Select Case Quarter

Case 1: Range("D:D,L:O,AD:AE,AN:AO,AX:AY,BK:BL,BW:BW,CC:CC").EntireColumn.Hidden = ShowColumns

Case 2: Range("E:E,P:S,AF:AG,AP:AQ,AZ:BA,BM:BN,BX:BX,CD:CE").EntireColumn.Hidden = ShowColumns

Case 3: Range("F:F,T:W,AH:AI,AR:AS,BB:BC,BO:BP,BY:BY,CF:CG").EntireColumn.Hidden = ShowColumns

Case 4: Range("G:G,X:AA,AJ:AK,AT:AU,BD:BE,BQ:BR,BZ:BZ,CH:CI").EntireColumn.Hidden = ShowColumns
End Select
End Sub