Display/Hide Columns Based on Drop Down List Selection

[JoRyBar]

Hello,

I am currently working on displaying and hiding all other columns in a range based on a drop down. In row 7 I have all the columns identified by text, which is the same as the drop down list. When an user selects the drop down, the sheet should hide all other columns except the one selected. The following code does not seem to work:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim R, V
If Target.Address = ("$f$3") Then
V = [F3].Value
For Each R In Range("J7:FV7")
If IsError(R.Value) Then
R.EntireColumn.Hidden = True
Else
R.EntireColumn.Hidden = R.Value <> V
End If
Next
End If
End Sub

I am not sure if it is because I have other VBA codes on the sheet or if the above is bad coding. I seem to always have issue with Private Sub Worksheet_Change(ByVal Target As Range) code.... Not sure if that is the issue....

Help Gurus!

Thanks!

Novice

 

[Fluff]

How about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Fnd As Range
   If Target.Address(0, 0) = "F3" Then
      Range("J:FV").EntireColumn.Hidden = True
      Set Fnd = Range("F7:FV7").find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = False
   End If
End Sub

 

[JoRyBar]

Hi Fluff,

I get an error that points to Private Sub Worksheet_Change(ByVal Target As Range). It says "Compile error: Ambiguous name detected: Worksheet_Change"

I am not sure how to fix this.

 

[Fluff]

You need to remove the code that you already have.

 

[JoRyBar]

Hi,

I need the other code for date stamping (see below):

Private Sub Worksheet_Change(ByVal Target As Range)
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("S:S,U:U"), Target)
xOffsetColumn = 1
If Not WorkRng Is Nothing Then
Application.EnableEvents = False
For Each Rng In WorkRng
If Not VBA.IsEmpty(Rng.Value) Then
Rng.Offset(0, xOffsetColumn).Value = Now
Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm"
Else
Rng.Offset(0, xOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
End Sub

Is there any way we can make it so that both codes can work on the same sheet?

Thanks!

 

[Fluff]

try

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim WorkRng As Range
   Dim Rng As Range, Fnd As Range
   
   Set WorkRng = Intersect(Range("S:S,U:U"), Target)
   If Not WorkRng Is Nothing Then
      Application.EnableEvents = False
      For Each Rng In WorkRng
         If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, 1).Value = Now
            Rng.Offset(0, 1).NumberFormat = "dd-mm-yyyy, hh:mm"
         Else
            Rng.Offset(0, 1).ClearContents
         End If
      Next
      Application.EnableEvents = True
   End If
   If Target.Address(0, 0) = "F3" Then
      Range("J:FV").EntireColumn.Hidden = True
      Set Fnd = Range("F7:FV7").find(Target.Value, , , xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Fnd.EntireColumn.Hidden = False
   End If
End Sub

 

[JoRyBar]

Almost! But it only shows 1 out of 13 columns. Each drop down task (which there are 12 choices) has 13 associated columns. The formula only shows the first of the columns, but hides the other 12.

Also, is there a way that all the columns can unhide when the drop down is blank?

Thank you so much for your speedy responses!!

 

[Fluff]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim WorkRng As Range
   Dim Rng As Range, Cl As Range

   Set WorkRng = Intersect(Range("S:S,U:U"), Target)
   If Not WorkRng Is Nothing Then
      Application.EnableEvents = False
      For Each Rng In WorkRng
         If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, 1).Value = Now
            Rng.Offset(0, 1).NumberFormat = "dd-mm-yyyy, hh:mm"
         Else
            Rng.Offset(0, 1).ClearContents
         End If
      Next
      Application.EnableEvents = True
   End If
   If Target.Address(0, 0) = "F3" Then
      If Target.Value = "" Then
         Range("J:FV").EntireColumn.Hidden = False
      Else
         For Each Cl In Range("J7:FV7")
            Cl.EntireColumn.Hidden = Cl.Value <> Target.Value
         Next Cl
      End If
   End If
End Sub

 

[JoRyBar]

Eureka! I think you did it!!!

Thank you thank you

 

[Fluff]

Glad to help & thanks for the feedback