The possibilities a

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.


"Dave Peterson" wrote:

I don't think you gave all the possibilities.

How about filling all the possibilities in that chart?

laststraw wrote:

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!

"Dave Peterson" wrote:

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.)

laststraw wrote:

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).

"Dave Peterson" wrote:

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

laststraw wrote:

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!!

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson