Hiding columns with a checkbox toggle

[USMCBBrady]

Hi all.

I'm currently working on an Excel book that allows users to select, via checkboxes, what columns will be visible in another sheet. I did some initial code a while back and seemed to have it, but when I made some code today, the sheet in question (the one with the hidden columns) 'freezes' for lack of a better term. It ends up at column M, and I can't scroll right. Below is the code.

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then
        Application.ScreenUpdating = False
        Application.Goto reference:="aetnaplan2"
        Selection.EntireColumn.Hidden = False
        Sheets("Plan Selections").Select
        Application.ScreenUpdating = True
    Else
        Application.ScreenUpdating = False
        Application.Goto reference:="aetnaplan2"
        Selection.EntireColumn.Hidden = True
        Sheets("Plan Selections").Select
        Application.ScreenUpdating = True
   End If
End Sub

I'm sure this is a completely noob question, but I just can't seem to figure out the syntax. I've got code that unhides/hides entire sheets and rows, based on checkbox selections and such, but this is throwing me for a loop. Please either reply here or my work email at bryan@ddanzig.com. Your help is greatly appreciated as I begin my exploration into VBA.

Sincerely,

Bryan Brady

[johnske]

Hi Bryan, welcome to VBAX.

Firstly, what exactly is

Application.Goto reference:="aetnaplan2"

intended to do, is it a named range, and where-abouts is column M in relation to it?


This command is more usually used in this form

Application.Goto Reference:=Worksheets("Sheet1").Range("A154")

It may be better here if you could remove any sensitive data and upload the workbook as an attachment so someone can look at what you need to do...

Regards,
John

PS: You can edit your code so that it looks like mine (above) by using VBA tags....i.e. Put (vba) before your code and (/vba) at the end of the code {but replace the normal brackets with square brackets}

[USMCBBrady]

Thank you very much for the quick reply. I switched around the code a little bit, to format it closer to what you suggested. I used the application.goto reference for another bit of code that counts up blank rows in a range and hides them. Still didn't help me here.

The problem appears to be that Column G, when selected, started at G2 because Row 1 was a merged row. I unmerged the row and now it appears to be working.

Tricksies Excel!

[johnske]

Hi Bryan,

Yes, merged cells often cause problems so if you have something that does something strange that you can't work out, the best approach is to first unmerge any merged cells and see if it still does it.

Glad to see you've got it worked out.

Regards,
John