12-30-2019 07:28 PM
12-30-2019 07:28 PM
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
Sheets("Sheet1").Columns("G").EntireColumn.Hidden = False //if checked the column is visible
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.
12-30-2019 08:13 PM
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.
12-30-2019 08:49 PM
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.
12-30-2019 10:39 PMSolution
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