Automatically hide/unhide columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a validation box with yes/no options in it in cell H7.
When "no" is selected I would like to hide columns M and R only. When yes
is selected I would like these columns to be displayed.

In the same worksheet I have a validation in cell H6 but would like columns
P:S hidden if "Total $" is selected from the validation list, columns K:N
hidden if "$/eq T" is selected and columns K:S visible if "Total $ & $/eq T"
is selected.

I need the first validation to work in conjunction with the second so that
for example, if total $ is selected and H7 has "yes" selected, only columns
K:N are visible.

I am working with Excel 2003 and my knowledge of VBA is extremely basic,
extending only to pasting code into a module.

I am very grateful for any help you can give me on this one. Thanks!!
 
This actually just looks at H7 to see if it's a Yes. If it's a yes, then M and
R are hidden. Anything else and M and R are shown.

If that's ok, rightclick on the worksheet tab that should have this behavior.
Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub

If Not (Intersect(Target, Me.Range("H7")) Is Nothing) Then
'in H7
Me.Range("M1,R1").EntireColumn.Hidden _
= CBool(LCase(Target.Value) = LCase("no"))
ElseIf Not (Intersect(Target, Me.Range("H6")) Is Nothing) Then
Me.Range("P:S").EntireColumn.Hidden _
= CBool(LCase(Target.Value) = LCase("total $"))
Me.Range("k:n").EntireColumn.Hidden _
= CBool(LCase(Target.Value) = LCase("$/eq t"))
End If

End Sub
 
Thanks Dave - it works like a charm except for when "$/eq T" is selected with
H7 = "yes". In this case it also displays column M which needs to be hidden
when $/eq T is selected (all K:N need to be hidden when $/eq T is selected).
 
I'm not sure I understand the requirements.

You have two cells to inspect (H6 and H7).

There's only a few things that can happen:


H7 H6 What should be hidden/shown
===== ================ =================================================
Yes Total $
Yes $/eq T
Yes Total $ & $/eq t
Yes blank

no Total $
no $/eq T
no Total $ & $/eq t
no blank

blank Total $
blank $/eq T
blank Total $ & $/eq t
blank blank

Please explain what columns should be hidden/shown for each of these 12
conditions.

(I didn't want to guess.)
 
Dave,

My apologies for not making this a bit clearer. What I have is something
like this...

Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T
Variance: (cell H7) validation list = Yes, No

K L M N O P Q R S T
---- Total Cost ---- ---- Unit Cost ----
2007 2008 var 2007 2008 var
34 30 4 26 28 -2
etc

If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be
displayed. If H7 = "No" then col M & R need to be hidden. This works fine
using the previous code.
However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed.
This combination currently displays col M as well which needs to remain
hidden if this combination is selected.
Is there an easy fix to this?

Thanks so much!
 
I don't think you gave all the possibilities.

How about filling all the possibilities in that chart?
Dave,

My apologies for not making this a bit clearer. What I have is something
like this...

Cost type: (cell H6) validation list = Total $ & $/eq T, Total $, $/eq T
Variance: (cell H7) validation list = Yes, No

K L M N O P Q R S T
---- Total Cost ---- ---- Unit Cost ----
2007 2008 var 2007 2008 var
34 30 4 26 28 -2
etc

If H6 = "Total $ & $/eq T" and H7 = "Yes", then K:M & P:R need to be
displayed. If H7 = "No" then col M & R need to be hidden. This works fine
using the previous code.
However, if H6 = "$/eq T" and H7 = "Yes" then only P:R should be displayed.
This combination currently displays col M as well which needs to remain
hidden if this combination is selected.
Is there an easy fix to this?

Thanks so much!
Click to expand...
 
The possibilities are:

H7 H6 Columns to be hidden / displayed

yes total $ & $/eq T none / all
yes total $ P:R / K:M
yes $/eq T K:M / P:R

no total $ & $/eq T M & R / K:L & P:Q
no total $ M & P:R / K:L
no $/eq T K:M & R / P:Q

The only scenario that the code doesn't do as I need is "yes" + "$/eq T"
because col M remains visible. The rest works great.
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'one cell at a time!
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("h6:h7")) Is Nothing Then Exit Sub

'I'd show everything to start
Me.Columns.Hidden = False

Select Case LCase(Me.Range("h7").Value)
Case Is = "yes"
Select Case LCase(Me.Range("H6").Value)
Case Is = "total $ & $/eq t"
Me.Columns.Hidden = False
Case Is = "total $"
Me.Range("P:R").EntireColumn.Hidden = True
Me.Range("K:M").EntireColumn.Hidden = False
Case Is = "$/eq t"
Me.Range("P:R").EntireColumn.Hidden = False
Me.Range("K:M").EntireColumn.Hidden = True
End Select
Case Is = "no"
Select Case LCase(Me.Range("H6").Value)
Case Is = "total $ & $/eq t"
Me.Range("m:M,R:r").EntireColumn.Hidden = True
Me.Range("K:L,P:Q").EntireColumn.Hidden = False
Case Is = "total $"
Me.Range("M:M,P:R").EntireColumn.Hidden = True
Me.Range("K:L").EntireColumn.Hidden = False
Case Is = "$/eq t"
Me.Range("K:M,R:R").EntireColumn.Hidden = False
Me.Range("P:q").EntireColumn.Hidden = True
End Select
Case Else
'do nothing
End Select

End Sub
The possibilities are:

H7 H6 Columns to be hidden / displayed

yes total $ & $/eq T none / all
yes total $ P:R / K:M
yes $/eq T K:M / P:R

no total $ & $/eq T M & R / K:L & P:Q
no total $ M & P:R / K:L
no $/eq T K:M & R / P:Q

The only scenario that the code doesn't do as I need is "yes" + "$/eq T"
because col M remains visible. The rest works great.
Click to expand...
 
This works except the last Case is = $/eq T needs to have the true and false
around the other way.

Thanks for your help - it's very much appreciated!
 
So you changed the true and false around and it worked ok?
This works except the last Case is = $/eq T needs to have the true and false
around the other way.

Thanks for your help - it's very much appreciated!
Click to expand...
 
Back
Top