Using VBA to intelligently unhide and hide columns

**Aegwyn11** · 10-25-2012, 01:26 PM

I'd like to use ActiveX checkboxes to unhide/hide columns. I'll have a number of checkboxes (for this example 3) referenced to a bunch of columns. The columns will start hidden. When a checkbox is checked, the columns assigned to it will unhide. When it is unchecked, I'd like them to hide. This is simple enough, but where I get confused is adding intelligence. Here's my example:

Checkbox 1 - tied to columns C and D
Checkbox 2 - tied to columns D and E
Checkbox 3 - tied to columns E and F

Here's a step by step that describes my problem:
Step 1 - check checkbox 1 - columns C and D unhide
Step 2 - check checkbox 2 - column E unhides (D is already unhidden because of step 1)
Step 3 - uncheck checkbox 1 - column C hides (but not D, because checkbox 2 is checked)

I understand I could do this with a bunch of IF statements to check the status of other boxes when one is unchecked, but as I add checkboxes this will make the code very unwieldy and non-scalable. Is there a simpler, cleaner way to do this?

Thanks in advance.

**JBeaucaire** · 10-25-2012, 02:10 PM

If you're going to expand on this overlapping of column triggers with multiple checkboxes, you're probably going to HAVE to create some reference table to manage it.

In a table on another sheet, your checkboxes would actually enter ON/OFF flags into the table. Each checkbox would put its ON/OFF references into a specific row, each column in the table represents the columns you're hiding/unhiding on the main sheet.

So if checkbox1 is clicked then ON would be put in C3 and D3, because it's tied to columns C and D.
If checkbox 2 is clicked, it would put ON in D4 and E4.
If checkbox 1 is uncchecked, the OFF is put in C3 and D3, but D4 would still have the ON from checkbox2.

At the top of the table is a formula that looks down the entire column looking for any instances of ON. So, in A1 is a formula:

=IF(COUNTIF(A$2:A$100, "ON")>0, "ON", "OFF")

...copied to the right for as many columns as needed. You get the idea.

Now no matter what check box you click, it only affect its own row, and the formulas at the top decide if the column itself if OFF/ON.

Lastly, create a macro that goes across Row1 and hides/unhides columns based on those flags. Call that macro as the last thing each checkbox does.

See if you can put that together.

**Aegwyn11** · 10-27-2012, 01:47 PM

Simple, easy, and works. Thanks

**Cutter** · 10-27-2012, 05:12 PM

@ Aegwyn11

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.