macro to hide columns (2003)

Topic

New Reply

Hello Everyone,

I need some help. I have a file that has a few columns and would like to create a macro and a combobox to hide the columns that are not selected. My purpose for doing this is to have the user only input in the column that was selected by the combobox. This will create less error. I am attaching a small sample of a file for references.

Reply | Quote

Replies

I’m not sure I understand your description entirely, but does the attached version do what you want?
The combo box in cell A1 is the result of Data | Validation.
There is code in the worksheet module for Sheet1 to show/hide columns.

Reply | Quote

Thanks Hans it does what I need, but how about if I need to display all the columns for printing. Can we put it in the combobox?

Reply | Quote

Does this do what you want? The first item in the list should display all columns.

Reply | Quote

See the attached modified version. I added a new value “All” in B1 (column B stays hidden) and adjusted the code. If “All” is selected, all Test columns are displayed.

Reply | Quote

All doesn’t seem to display all Test columns.

Reply | Quote

Oops, you’re correct. I didn’t take into account that End(xlToLeft) operates on visible columns only.

Easily repaired by changing

For i = 3 To Range(“IV1”).End(xlToLeft).Column
Columns(i).Hidden = False
Next i

to

For i = 3 To 256
Columns(i).Hidden = False
Next i

Thanks for testing!

Reply | Quote

I didn’t really test. I looked at your code to see how similar it was to mine and saw that you made the same error I did on my first try.

Reply | Quote

Reply | Quote