Hello Experts,
How do you hide & unhide columns based on a dropdown menu selection?
Across row 2, I have column headings named Budget, Actual, Variance,
Percentage. Using a dropdown Data Validation List, I'd like to hide the
selected columns.

The dropdown menu selection would yield the following result:
Variance - only Variance column will hide.
Percentage - only Percentage column will hide.
Variance and Percentage - both columns will hide.
Show All - both Variance and Percentage will unhide.

I have approximately 100 columns. So this code will alleviate a lot of
manual hide and unhide. I'd appreciate your input.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ricky,

Use the View - Custom Views menu to save each set of hidden/visible columns
as a custom view. Then name a cell "rngValidation" and build a Data
Validation List in that cell that selects the names of each view you would
like to show. Place a formula somewhere on the sheet that refers to the
"rngValidation" cell (just =rngValidation will do).

Then use a Worksheet Calculate macro to change views based on the contents
of rngValidation.

For instance; if the Data Validation List contains two entries - Variance
and Percentage, and theses correspond to two views = one and two, then you
can place this code in the worksheet code pane to switch between views.

Private currView As String

Private Sub Worksheet_Calculate()
If ActiveSheet.Range("rngValidation") < currView Then
Select Case ActiveSheet.Range("rngValidation")
Case "Variance"
ActiveWorkbook.CustomViews("one").Show
Case "Percentage"
ActiveWorkbook.CustomViews("two").Show
End Select
End If
currView = ActiveSheet.Range("rngValidation")
End Sub


Ed Ferrero
http://edferrero.m6.net

"Ricky Pang" wrote in message
...
Hello Experts,
How do you hide & unhide columns based on a dropdown menu selection?
Across row 2, I have column headings named Budget, Actual, Variance,
Percentage. Using a dropdown Data Validation List, I'd like to hide the
selected columns.

The dropdown menu selection would yield the following result:
Variance - only Variance column will hide.
Percentage - only Percentage column will hide.
Variance and Percentage - both columns will hide.
Show All - both Variance and Percentage will unhide.

I have approximately 100 columns. So this code will alleviate a lot of
manual hide and unhide. I'd appreciate your input.

Thanks in advance,
Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ed, thanks for your help. A few questions...the macro doesn't seem
to function when I select from the data validation list and the spelling
is correct. I had to clear the validation list and manually type in
"Variance" for it to hide that column. How do I get it working using
the dropdown menu?

You wrote: Place a formula somewhere on the sheet that refers to the
"rngValidation" cell (just =rngValidation will do). Why must there be
this single cell floating within the spreadsheet in order for the input
cell where the dropdown menu is to function? I thought just by having
the dropdown menu selection within the spreadsheet is adequate to
trigger the macro?

I've named the Custom Views as Variance, Percentage, Variance and
Percentage, and Show All. Could you please clarify when I should have
added the "one" and "two" to the Custom Views? I couldn't make out what
the "one" or "two" in these lines mean. I've also tried typing
Percentage, but nothing happens.
Case "Variance"
ActiveWorkbook.CustomViews("one").Show

Lastly, would a search for "Variance" headings in Row 2, then hide those
columns be a viable option? Or, a search for "Percentage" and hide?
Using functions, I can only Find "Variance" headings one at a time then
hide each column but no way of highlighting all headings with the same
name then hide.

Thanks again Ed. Much much appreciate.

Ricky

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Hi Ricky,

You wrote: Place a formula somewhere on the sheet that refers to the
"rngValidation" cell (just =rngValidation will do). Why must there be
this single cell floating within the spreadsheet in order for the input
cell where the dropdown menu is to function? I thought just by having
the dropdown menu selection within the spreadsheet is adequate to
trigger the macro?

I suggested that the macro is lauched by the Worksheet Calculation
event, so we need something that calculates when the dropdown changes.

I've named the Custom Views as Variance, Percentage, Variance and
Percentage, and Show All. Could you please clarify when I should have
added the "one" and "two" to the Custom Views? I couldn't make out what
the "one" or "two" in these lines mean. I've also tried typing
Percentage, but nothing happens.
Case "Variance"
ActiveWorkbook.CustomViews("one").Show

You can name the views as anything you like. If your view is named
"Variance", then substitute "Variance" where I have used "one" in
the macro. I have placed a small sample worksheet on my web site
to show you what I mean.

Lastly, would a search for "Variance" headings in Row 2, then hide those
columns be a viable option? Or, a search for "Percentage" and hide?
Using functions, I can only Find "Variance" headings one at a time then
hide each column but no way of highlighting all headings with the same
name then hide.

You could do it that way, but it does not seem to be as flexible as using
views.

Ed Ferrero
http://edferrero.m6.net

Thanks Ed, this what I'm looking for. It was very helpful that you made
the sample spreadsheet and now I have your code in Sheet1. A few
questions: Everytime, I open other files, this macro kicks in and I get
this error message:
Run-time Error '1004': Application Defined or Object Defined Error.
It's as if the macro is also trying to find the rngValidation cell/range
and all of the custom views in the newly opened file which doesn't
exist. How can I troubleshoot this issue? Your file is fine and never
produces this error message when I open another file.

I will need to make 10 more duplicate tabs with this capability to
customize hidden columns. Each tab will consists of 155 columns by 330
rows. With this code in place, I notice that Create a Copy takes much
longer compared to not having it. Is it because it's calculating within
the code, in addition to the tab's regular calculations (no links or
updates in my calculations)?

I notice that choosing the Custom View on a duplicate tab displays
original tab's view. Is there a way to set the views of hidden columns
so that when I duplicate them, the macro will hide the activesheet? That
way, I don't have to set up Custom Views in every tab if the hidden
columns are the same in each tab? This is just trying to save some
time.

Another issue. But it's building on this hidden column posting so I'll
just continue here. It's for my Summary tab. Using a button to toggle
between different views for hidden columns, how do I make name/text of
the button to reflect the name of each view everytime I click the
button? For example, columns B to D are hidden, the button's name is
View1. Only columns C to E are hidden, the button's name is View2.
Only columns D to F are hidden...View3. The next click goes back to
View1 to repeat the cycle again.

It would be helpful to know how to hide columns using a Validation list
and a toggle button way.

Thanks very much for your expertise. I've gone through your website and
already, I have gotten some valuable ideas on how to improve my
spreadsheets.

Ricky


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!