Unhide X number of columns based on Value in cell

[kellem80]

Hello,
I hope somebody can help. I have a template I need to send out to multiple users (most of whom are NOT excel savvy). One of the items they need to complete is a dropdown with 10 choices. I have the data validation set for this and defaulted to choice of "NA" which they will change to their choice if appropriate. however, they can have up to all of the 10 choices to choose. I want to keep all templates formats the same (all info in each column tracks). I don't want to have all 10 columns visible, if possible, just the first, but have some indicator that would unhide the columns if necessary.
My thought process is this:
In column K would be the first Data Validation choice
In column L they would indicate how many more options they need 0-9
Based on the response, the sheet would unhide the next 0-9 columns which are already properly formated with the data validation. (0 would result in no more columns being made visible)

Is there code that will cover this conundrum? or a simple formula? preferable something that doesn't require the enabling of macros? If that is the only option, its doable, just not preferred (will have to explain how to enable the macros to all the users)

Oh---using 2007.

thanks in advance to your input!!


km

 

[svkroy]

hey,

one question, if user selects 1 in col "L", then col "M" would unhide, or if user selects 2 in col "L" then M & N col would unhide, am i right ?

If I am, then the easiest option i see is a macro.

 

[kellem80]

You are correct. I figured a macro. I already have to use code to make the validation list wider, so if you know one that will work for unhiding the columns, I'm ready!

 

[svkroy]

You are correct. I figured a macro. I already have to use code to make the validation list wider, so if you know one that will work for unhiding the columns, I'm ready!

one more question, which cell in column L would the user be inputtting the values into ?

See my idea is simple, if cell value=1, then unhide "M" column. I think Select Case statement would be best used for this, or a few If statements.

 

[kellem80]

It would apply to the entire column. They users are inputting information for different projects along a row (1 Project= 1 Row, multiple columns). Basically, to not overwelm the users we would like to keep unnecessary columns hidden. I have a script in the end that will consolidate all the different workbook sheets into one, so I want to keep the columns the same whether they have input or not, thats why the DV defaults to "not applicable" and they have to change to their chosen option.
I'm new ot the macros and code, so not sure what the syntax for such a thing would be.

 

[svkroy]

It would apply to the entire column. They users are inputting information for different projects along a row (1 Project= 1 Row, multiple columns). Basically, to not overwelm the users we would like to keep unnecessary columns hidden. I have a script in the end that will consolidate all the different workbook sheets into one, so I want to keep the columns the same whether they have input or not, thats why the DV defaults to "not applicable" and they have to change to their chosen option.
I'm new ot the macros and code, so not sure what the syntax for such a thing would be.

Ok, I understand your point, but what if in L1 cell user puts 1 and L2 cell user puts 2, how many columns then would you like the macro to unhide ?

 

[kellem80]

I see your point, my forward thinking brain wasn't there yet.
In the BEST situation, the columns would go back to being hidden after the final entry has been submitted, basicall when Column U is changed from NA and a new entry is started in row two (A2). Only I know the users will jump around as they complete the template (ex: filling in all the basics in columns A-J, then completing the choices in K-U). What about a visible column V where they would have to answer Y if they have completed all choices for that row and use the hide function, then they could hide and unhide row by row......

Remember, not savvy users, so letting them know they can unhide/hide it themselves, not going to go over well.

I'm beginning to think buttons may be preferrable: simple one that says "Show More Options" that will unhiode all the options and one that says "Show Less Options" that will hide all but the first option
Im good with button, just not the hide/unhide argument when they press the button.....


Sorry for the ramble, thinking it through as i type.

 

[svkroy]

I see your point, my forward thinking brain wasn't there yet.
In the BEST situation, the columns would go back to being hidden after the final entry has been submitted, basicall when Column U is changed from NA and a new entry is started in row two (A2). Only I know the users will jump around as they complete the template (ex: filling in all the basics in columns A-J, then completing the choices in K-U). What about a visible column V where they would have to answer Y if they have completed all choices for that row and use the hide function, then they could hide and unhide row by row......

Remember, not savvy users, so letting them know they can unhide/hide it themselves, not going to go over well.

I'm beginning to think buttons may be preferrable: simple one that says "Show More Options" that will unhiode all the options and one that says "Show Less Options" that will hide all but the first option
Im good with button, just not the hide/unhide argument when they press the button.....


Sorry for the ramble, thinking it through as i type.

I apprehend the trigger for the macro would be any change in the worksheet, so user interaction is least solicited (buttons are optional), the cell values would determine how many rows to unhide and it would be done realtime........can you post a copy of the sheet........as day by day im getting more confused none the less.

 

[kellem80]

Unfortunately, I cannot post my workbook or SS, however:

I ended up trying some code with the button:

With Columns("L:V")

If .Hidden Then

.Hidden = False
​

Else

.Hidden = True
​

End If
​

End With
End Sub

So as they complete they can expand the list, when they are done, they can collapse it back up!! I have several sections I will need to repeat this for, but for me this works and allows the user the flexibility to open and close and go back and change things if necessary.
Did it for 2 sets of 10 column choices and worked well----so far!

Thanks for questioning my orginal post!! It made me think a bit more and forced me to work it out!!