Display Hidden Columns Based On A Value In A Cell

U

unlikeKansas

Is there a way to display a range of hidden columns based on the value entered in a cell

E.G. Validation has limited the values a user can enter in Cell A1 to A,B,C,D or E. If the user enters A in A1 I want to unhide columns B, G & L. If they enter B in A1, I want to unhide columns C, H & M. Entering C in A1 would unhide D, I & N, etc.
 
D

Dave Peterson

You could use a worksheet event that looks for a change.

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)

Dim myRng As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

Select Case LCase(Target.Value)
Case Is = "a": Set myRng = Me.Range("b1,g1,L1")
Case Is = "b": Set myRng = Me.Range("c1,h1,m1")
Case Is = "c": Set myRng = Me.Range("d1,i1,n1")
Case Is = "d": Set myRng = Me.Range("e1,j1,o1")
Case Is = "e": Set myRng = Me.Range("f1,k1,p1")
Case Else
Set myRng = Nothing
End Select

If myRng Is Nothing Then
'do nothing
Else
'hide it all???
Me.Range("b1:p1").EntireColumn.Hidden = True
myRng.EntireColumn.Hidden = False
End If

End Sub

But if you're using xl97, there's a problem with worksheet_change events not
firing when using data|validation.

From Debra Dalgleish's site:
http://www.contextures.com/xlDataVal08.html

In Excel 97, selecting an item from a Data Validation dropdown list
does not trigger a Change event, unless the list items have been typed in
the Data Validation dialog box. In this version, you can add a button to
the worksheet, and run the code by clicking the button. To see an
example, go to the Sample Worksheets page, and under the Filters
heading, find Product List by Category, and download the
ProductsList97.xls file.
 
Top