VBA. unhide columns based on value in one specific cell.

[rain4u]

Hi
Right. Where to start
On a sheet called "column hide settings" I have some names in range C2:C6 (i.e. All, Section 1, Section 2, Section 3, Section 4) and some values in D2:D6 (i.e H:M, Q:U, Y:Z, AD:AP H:M Q:U Y:Z AD:AP) as below:

All	H:M, Q:U, Y:Z, AD:AP
Section 1	H:M
Section 2	Q:U
Section 3	Y:Z
Section 4	AD:AP

Now on another sheet (within same workbook) I have a dropdown box which is linked to Sheets("column hide settings").Range("F2").
Now in Sheets("column hide settings").Range("F3") I have offset formula which is essentially picking up the respective value from D2:D6 of picked section.
In my real workbook I will be using dynamic named ranges for both of these tables (e.g SourceList_DYNAMIC_column_hide_settings), just to keep it as dynamic as possible so I can add/change settings on the fly.


What I'm trying to achieve is to use these values to un-hide certain columns.

Below is my current code which work to a extent:

Please Login or Register  to view this content.

This code works with the Section 1 to section 4 values, but it errors out with the top one. How to fix my current code so It would work with all options.

Another option would be to loop through D3:D (Like I said I will have dynamic named range "SourceList_DYNAMIC_column_hide_settings", so if the list would grow I would like it to still work). Unfortunately I'm still terribly new in VBA syntax and looping.

Could someone help me out with this. I'm sure someone else might find it useful in future. I would be very thankful as always.


example xls attached.

[Dennis7849]

Here is the answer:

Step 1: remove the spaces in the "All" definition. Was H:M, Q:U, Y:Z, AD:AP, now H:M,Q:U,Y:Z,AD:AP

Step 2: change this

Columns("" & columnSection & "").EntireColumn.Hidden = False

to

Range(columnSection).EntireColumn.Hidden = False

[nilem]

Try this option, without button. Just choose a value from the combobox.

[rain4u]

Thank you both!
Dennis. I was pretty close. thank you.



Nilem
Thank you. Very interesting technique. Absolutely amazing stuff to learn. In this particular spreadsheet it will be a better option as its a spreadsheet that is shared". My current method would always have the issue with that linked cell. When people would change the drop down it would create a conflicting value in that linked cell. That can become a problem.


Just out of curiosity. How to use same approach with "Forms" combobox, rather than "Control Toolbox" combobox?

I created a forms combobox named "Drop_Down_345" and used 'column hide settings'!$C$2:$D$6 as an input range.


Now I used your current code (as below) to learn and take as an example

Please Login or Register  to view this content.

So now I created a code something along these lines:

Please Login or Register  to view this content.

It errors out with Range(Drop_Down_345.Value).EntireColumn.Hidden = False
Any idea how to correct this syntax?
Please see the spreadsheet attached.



I would appreciate your or anyones elses help in this matter.
Cheers

[nilem]

Here is an example for the Forms control

[rain4u]

Hi Nilem.
Thank you for the response. The example code is working but how could I make it dynamic? By this I mean that the current code

Please Login or Register  to view this content.

has this hard coded section vRanges = Array("H:M,Q:U,Y:Z,AD:AO", "H:M", "Q:U", "Y:Z", "AD:AP").
I would like to make it dynamic in a same way as it was with your previous example, where the user could easily change the values (settings) in D2:D6 of sheet "column hide settings" and the outcome would be different. Could you or anyone else try to help me to achieve this with forms combobox (all my workbooks all have forms combo boxes). I would be very much interested to learn this and change some of my previous workbooks to a method where there is no linked cell involved.

Any help is extremely appreciated.

[nilem]

try it

Please Login or Register  to view this content.

[rain4u]

Hi Nilem
Thanks. This works brilliantly. I have just one more favor to ask and I promise I will be really happy then.
Like I said in the beginning of my original post, I will be using dynamic ranges on my original workbooks.

So I have created these two dynamic ranges:
dropdown_input
dropdown_output


dropdown_input essentially covers range C2:C6 of sheet "column hide settings"
e.g. it picks up the following values from that range
All
Section 1
Section 2
Section 3
Section 4

Should user type in more options lets say "Section 5" and "Section 6" then the this named range dropdown_input would automatically expand and would cover C2:C8 (instead of C2:C6). I'm sure you are familiar with dynamic named range concept.
This named range is used in combobox's "input range" so the user can see all the available options when clicking on the dropdown box. So now the user would see options:
All
Section 1
Section 2
Section 3
Section 4
Section 5
Section 6



dropdown_output essentially covers range D2:D6 of sheet "column hide settings"
e.g. it picks up the following values from that range
H:M, Q:U, Y:Z, AD:AP
H:M
Q:U
Y:Z
AD:AP
AS:AV

Again user could create more and type in lets say "AS:AV" and "AX:AZ" then again just like with previous example this named range dropdown_output would automatically expand and would cover D2:D8 (instead of D2:D6).
So it would pick up values:
H:M, Q:U, Y:Z, AD:AP
H:M
Q:U
Y:Z
AD:AP
AS:AV
AS:AV
AX:AZ


Now here comes the tricky part that I can not figure it out myself. I would like to use this named range dropdown_output within the code.
I was thinking something along the lines of:

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

I have attached new workbook with these two dynamic named ranges
Unfortunately I can not get it right. Could you or anyone else help me one more time? I would appreciate the help.
Cheers

[nilem]

Please Login or Register  to view this content.

[rain4u]

Thank you Nilem.
Your soltuion is perfect. I have also learned so much over the past few days regards forms comboboxes. Thank you again.