How to limit columns that display based on selection of a dropdown value

[Hrwilkers]

New User to this this site. Great site, have found a lot of grea
information.
Have not been able to find how to do this:

I have a Worksheet that I want to limit the columns that display base
on a value selected in a dropdown list. This sheet is a workshee
that data entry will be completed on. The worksheet currently onl
contains a header row.

The value in the dropdown list only contains two values: ValueA
ValueB
The dropdown list was created using data validation, Allow list, an
typing in ValueA, ValueB.

Column B Contains the dropdown list of ValueA, ValueB
What I want to do is:
If ColumnB contains ValueA, then display columns: A through H and K
If ColumnB contains ValueB, then display columns: A through C, I an
J

How can I accomplish this?
Thanks for the assistance as I am stumped. HRwilker

 

[squenson via OfficeKB.com]

Copy this code into the Sheet that is used (ALT+F11, then select the proper
sheet on the left pane, then copy the code on the right pane. Note that the
code can be more compact as we can hide/display contiguous columns in one
line of code, but I voluntarily made it extensive for maintenance purposes.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$B$1" Then
Exit Sub
End If

Select Case Target.Value
Case "ValueA"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = True
Columns("J").Hidden = True
Columns("K").Hidden = False
Case "ValueB"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = True
Columns("E").Hidden = True
Columns("F").Hidden = True
Columns("G").Hidden = True
Columns("H").Hidden = True
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = True
End Select

End Sub

 

[Hrwilkers]

This works, but only for one cell. I need it to work for the entir
column from B2 down. My guess is I need to use a range and modify thi
line, but can not figure out what to modify it with:
If Target.Address <> "$B$1" Then

How do I modify this to apply to all of column B from B2 down? Thanks
Hrwilker

 

[Rick Rothstein \(MVP - VB\)]

This works, but only for one cell. I need it to work for the entire

column from B2 down. My guess is I need to use a range and modify this
line, but can not figure out what to modify it with:
If Target.Address <> "$B$1" Then

How do I modify this to apply to all of column B from B2 down? Thanks.

I'm not sure I understand what you mean by "apply to all of column B from B2
down". Do you mean you want a ValueA or ValueB setting in ANY cell of column
B (except B1) to trigger the hiding of the columns you indicated? If so,
what about when some of the cells in column B are set to one value and the
remaining cells are set to the other value.. which cell governs the hiding
operation? Can you explain what you are trying to do in a little more
detail?

Rick

 

[Hrwilkers]

Completed, Thanks for the Assistance. At first when I tried this I was
getting a Run Time Error "13", I then added an If Error statement and
that got rid of the error.

The code below will Display certain columns based on the value in
column C. If you click on a blank cell, then it displays all columns.
If you click on the header at the top, then it displays all columns, now
for the code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B:$B" Then
Exit Sub
End If
On Error Resume Next
Select Case Target.Value
Case ""
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Header Value)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = False
Case "(Value A)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = False
Columns("E").Hidden = False
Columns("F").Hidden = False
Columns("G").Hidden = False
Columns("H").Hidden = False
Columns("I").Hidden = True
Columns("J").Hidden = True
Columns("K").Hidden = False
Case "(Value B)"
Columns("A").Hidden = False
Columns("B").Hidden = False
Columns("C").Hidden = False
Columns("D").Hidden = True
Columns("E").Hidden = True
Columns("F").Hidden = True
Columns("G").Hidden = True
Columns("H").Hidden = True
Columns("I").Hidden = False
Columns("J").Hidden = False
Columns("K").Hidden = True
End Select

End Sub



Thanks to all who assisted. This was done in Excel Version 2002.

 

[Hrwilkers]

Sorry, It should read Column "B", NOT Column "C" as above. Jaosn