Hide columns using check boxes

[jumpcut]

I'm new to Excel programming but seem to be making progress and am enjoying the power and flexibility of VBA. I have a spreadsheet that has two sheets. Sheet1 contains 25 columns of data. At any given time I only want to display a few of them, so Sheet2 has 25 check boxes that hides/unhides the desired columns on Sheet1. All works well until I move a column on Sheet1. The check box and code to hide/unhide the column doesn't reference its new column position but, instead, references its original column position. Here's the code from one of the check boxes on Sheet2 that hides/unhides column G on Sheet1.

If Range("A8") = "False" Then   //A8 on Sheet2 is set to true or false by its corresponding check box 

Sheets("Sheet1").Columns("G").EntireColumn.Hidden = True   //if not checked the column is hidden

Else

Sheets("Sheet1").Columns("G").EntireColumn.Hidden = False   //if checked the column is visible

End If

Is there a way for the hide/unhide code to reference the same column no matter where it is moved by the user?

Many thanks for any help or suggestions.

[#2]

@jumpcut 

Does it work as desired?

Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Columns("G").Hidden = Not ws2.Range("A8")

[#3]

@Subodh_Tiwari_sktneer

Hello and Thank You for your reply. When I check or uncheck the check box I get the following dialog:

Run-time error '9':

Subscript out of range

[#4]

@jumpcut 

Tweak the Sheet Names in the following lines as per the actual sheet names in the workbook...

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

Here ws1 i.e. Sheet1 is the Sheet where you want to hide the columns and ws2 i.e. Sheet2 is the Sheet which contains the linked Cells which turn to True or False by clicking on CheckBoxes.

[#5]

@Subodh_Tiwari_sktneer 

Of course, I should have realized the names needed to change. Now, when I run this code everything works as before. Column G, which I have named "Notes" is visible/hidden by the check box. But if I move the "Notes" (G) column to another position (column C, for example) the check box still hides/unhides column G instead of the "Notes" column in its new position column C.

Thank you, again, for your reply.

[#6 · Accepted solution]

@jumpcut 

In that case, you should first locate a column with header "Notes" on Sheet1 and if it is found, you hide or unhide it irrespective of which column contains that header.

Please try it like this and don't forget to tweak the Sheet Names in the code...

Dim ws1         As Worksheet
Dim ws2         As Worksheet
Dim headerCell  As Range

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

'Assuming headers are in Row1 on Sheet1
Set headerCell = ws1.Rows(1).Find(what:="Notes", lookat:=xlWhole)

If Not headerCell Is Nothing Then
    headerCell.EntireColumn.Hidden = Not ws2.Range("A8")
Else
    MsgBox "The column with header 'Notes' was not found.", vbExclamation
    Exit Sub
End If

[#7]

@Subodh_Tiwari_sktneer 

Works perfectly! Many thanks for your help. A very Happy New Year to you!

[#8]

@jumpcut 

You're welcome! Glad it worked as desired.

Happy New Year to you too!