How to hide and unhide columns based off of a numeric cell value using VBA

[tommyboy1019]

Hey guys,

This is my first time posting, but I've definitely used the forums for help before.
I need to expand and collapse columns based off of the numerical value in cell A1. And I need to use VBA to do so.
Columns B:Z will expand and collapse. For ex. A1 = 0 then no columns show. A1=10 then B:K will show. If I go back to A1=0 then no columns will show again. If A1=25 then B:Z will show.

Please help, I have very armature knowledge in VBA. Any assistance will be appreciated.

Thanks!

 

[tommyboy1019]

Hello Dave, I've followed Yongles directions to a Tee for both of your codes. I was wondering if it was working on your end? I don't want everybody coming up with new solutions if it's something I'm doing wrong. But from what I can see, the code's in it's designated sheet. I wish I could post a pic for review.

 

[Joe4]

Is the code working for you by chance?

Yes, I tested dmt32's code and it does things when I enter values in.

If I enter 0 in cell A1, it hides columns B-Z.
If I enter 10 in cell A1, it hides columns L-Z.
If I enter 25 in cell A1, it unhides all columns.

To make sure that your code is being invoked, enter this line at the very top:

MsgBox "Code is invoked"

When, anytime you change the value in A1, you should see that MsgBox. If you do not, your code is not being invoked. You may have events disabled.
You can re-enable them by putting this code anywhere and running it once:

Sub ReenableEvents()
    Application.EnableEvents = True
End Sub

 

[dmt32]

Hello Dave, I've followed Yongles directions to a Tee for both of your codes. I was wondering if it was working on your end? I don't want everybody coming up with new solutions if it's something I'm doing wrong. But from what I can see, the code's in it's designated sheet. I wish I could post a pic for review.

I can only comment on my code which works ok for me - have you enabled macros?

• Dave

 

[tommyboy1019]

You guys, I must offer my apologies. I had the macros disabled in the spreadsheet that I was working on, thus the codes you were sending me were not working. I'm such a dingus sometimes. I really can't believe how fast you guys are with your responses. Needless to say, the codes are now working perfectly.

However, I was wondering if I had to create a case for every column that I wanted to unhide? For example B:Z are the columns I want to unhide based on cell value. Do I need to create 25 cases?

 

[Joe4]

Use a CASE statement, like Yongle was showing in his first post. That is probably the most efficient way to handle 25 cases.
If some unhide the same columns, you can combine them on the same line.
See here for examples: https://www.techonthenet.com/excel/formulas/case.php

 

[tommyboy1019]

I owe you an apology Dave, your code works. You seem to have all the answers. I had my macros disabled like a fool. Thanks for all your help with this. you rock. Quick question though. since I want to hide and unhide based off of cell value, do I need to create 25 cases for each column?

 

[Joe4]

do I need to create 25 cases for each column?

See my previous reply.

 

[tommyboy1019]

Joe you are lighting fast! I think I just might if nothing better comes up. Thank you for being a part of my thread, I really appreciate your help.

 

[dmt32]

You guys, I must offer my apologies. I had the macros disabled in the spreadsheet that I was working on, thus the codes you were sending me were not working. I'm such a dingus sometimes. I really can't believe how fast you guys are with your responses. Needless to say, the codes are now working perfectly.

No worries - got it working in the end thats the goal

However, I was wondering if I had to create a case for every column that I wanted to unhide? For example B:Z are the columns I want to unhide based on cell value. Do I need to create 25 cases?

Post what you are needing to do - someone here I am sure will come up with a very elegant answer

Dave

 

[Joe4]

Note that I am not 100% certain I am clear on the details of your last piece.

Are you saying 25 different cases, or 25 different cases for each column?
If the later, are the 25 different cases the same for each column?
If so, then you should be able to do it with just 25 different cases.

As Dave said, if you provide the details, we can probably help you out, or at least give you a better idea of how to attack it.