multiple checkboxes with multiple hidden columns

[MickJacko]

Hi there - thanks for your time in advance.

I am trying to create a report from excel that does the following:

i have my checkboxes in sheet 1, and i want to hide various columns if the checkbox is not selected in sheet 2. each checkbox is directly related to a column in sheet 2.

example:
In sheet 1, I have 5 check boxes labeled:
checkbox 1
checkbox 2
checkbox 3
checkbox 4
checkbox 5

i want to create any combination of the following:
when i tick checkbox 1, column A in sheet 2 is shown
when i do not tick checkbox 2, column B in sheet 2 is not shown
when i tick checkbox 3, column C in sheet 2 is shown
when i do not tick checkbox 4, column D in sheet 2 is not shown
when i tick checkbox 5, column D in sheet 2 is shown

Any help would be greatly appreciated to get this complete.

 

[nuc_n_futs]

Hi,

Using ActiveX checkboxes, you can do the following:

Once you have your checkboxes, double click when in design mode and Type in the following in between Private Sub and End Sub.

Application.ScreenUpdating = False
If checkbox1.Value = True Then
Sheet2.Select
Sheet2.Columns("A:A").Select
Selection.EntireColumn.Hidden = False
Elseif Checkbox1.Value = False Then
Sheet2.Select
Sheet2.Columns("A:A").Select
Selection.EntireColumn.Hidden = True
End If
Sheet1.Select
Application.ScreenUpdating = True

For Checkbox2
Application.ScreenUpdating = False
If checkbox2.Value = True Then
Sheet2.Select
Sheet2.Columns("B:B").Select
Selection.EntireColumn.Hidden = False
Elseif Checkbox2.Value = False Then
Sheet2.Select
Sheet2.Columns("B:B").Select
Selection.EntireColumn.Hidden = True
End If
Sheet1.Select
Application.ScreenUpdating = True

Follow the same logic for Checkbox3, changing the column to "C"

For Checkbox4

Application.ScreenUpdating = False
If (Checkbox4.Value = True And Checkbox5.Value = False) Then
Sheet2.Select
Sheet2.Columns("D:D").Select
Selection.EntireColumn.Hidden = False
ElseIf (Checkbox4.Value = True And Checkbox5.Value = True) Then
Sheet2.Select
Sheet2.Columns("D:D").Select
Selection.EntireColumn.Hidden = True
ElseIf (Checkbox4.Value = False And Checkbox5.Value = True) Then
Sheet2.Select
Sheet2.Columns("D:D").Select
Selection.EntireColumn.Hidden = True
End If
Sheet1.Select
Application.ScreenUpdating = True

Use the same Code from checkbox4 in checkbox5

Nuc

 

[MickJacko]

Thanks nuc_n_futs - like the nickname!!

Still having issues with it.

Is it possible for you to place it in an excel file and send to me?

Regards,

 

[nuc_n_futs]

Sure, send me a private message.

 

[mikerickson]

Another approach would be to use checkboxes from the Forms menu and assign all of them to this macro.

Sub CheckControlColumnVisiblity()
    Dim boxIndex As Long
    boxIndex = Right(Application.Caller, 1)
    With ActiveSheet
        .Columns(Val(Right(Application.Caller, 1))).EntireColumn.Hidden = (.Shapes(Application.Caller).ControlFormat.Value = xlOff)
    End With
End Sub