Macro - Checkbox to hide certain column (not always in the same location)

[largeselection]

I have a userform that I created which has a lot of checkboxes on it. Each checkbox corresponds to a column of data in my sheet. As of now, when I check the box, the column of data is made visible. So I can select which columns of data I want to see.

My question is how can I make the reference apply to a specific column of data rather than just the specific column.

Now my code has Columns("C:C").hidden=false.

How can I change this so that I can add columns before column C. As of now, if I insert a column at B then when I check off that box in my userform it hides the wrong column (since the data that was in column C has now been moved to column D).

Thanks for your help

 

[Bill Rockenbach]

You could search for a Header Name

Sub ShowColumn()
Dim MyColumn As Integer
Dim MyHeader As String
MyHeader = "Some Header Name"
For MyColumn = 1 To 26
  If Cells(1, MyColumn) = MyHeader Then 
    Columns(MyColumn).Hidden = False
    Exit For
  End If
Next MyColumn
End Sub

 

[largeselection]

So how would I work this into my checkbox code?

Dim MyColumn As Integer
Dim MyHeader As String
MyHeader = "Some Header Name"
For MyColumn = 1 To 26
Columns(MyColumn).Hidden = Not Checkbox2.value

Currently my code says:

Private Sub CheckBox2_Click()

Columns("J:J").Hidden = Not CheckBox2.Value

End Sub

How do I merge these two concepts together?

 

[Bill Rockenbach]

I assume you have Column Headers in row 1
Put this code in a standard module (not a sheet module)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Option Explicit<o:p></o:p>
Sub HideUnhideColumn(MyHeader As String, cbTrueFalse As Boolean)<o:p></o:p>
    Dim MyColumn As Integer<o:p></o:p>
    Const LastColumn As Integer = 25  '<<< change to Maximum number of columns<o:p></o:p>
    <o:p></o:p>
    For MyColumn = 1 To LastColumn<o:p></o:p>
        If Cells(1, MyColumn) = MyHeader Then<o:p></o:p>
            If cbTrueFalse = True Then<o:p></o:p>
                Columns(MyColumn).Hidden = False<o:p></o:p>
            Else<o:p></o:p>
                Columns(MyColumn).Hidden = True<o:p></o:p>
            End If<o:p></o:p>
                 Exit Sub<o:p></o:p>
        End If<o:p></o:p>
    Next MyColumn<o:p></o:p>
    <o:p></o:p>
End Sub

<o></o>
Then create this code in the sheet module for each check box.
<o></o>

Option Explicit<o:p></o:p>
 <o:p></o:p>
Private Sub CheckBox1_Click()<o:p></o:p>
    HideUnhideColumn "Employee Number", CheckBox1.Value<o:p></o:p>
End Sub<o:p></o:p>
 <o:p></o:p>
Private Sub CheckBox2_Click()<o:p></o:p>
    HideUnhideColumn "Employee Name", CheckBox2.Value<o:p></o:p>
End Sub

<o></o>
In this example there are two check boxes. One for a column titled Employee Number and one for a column titled Employee Name. You can have as many checkboxes as desired
<o></o>

 

[largeselection]

Perfect!

Thanks so much for your help!