Forum Discussion
Hide columns using check boxes
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.
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 Replies
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")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
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.
