VBA code error

[Nickiemay]

I am attempting to hide columns based on the selection made in a drop down list in B1. There are five options, four of which would result in certain columns hiding. "Select" hides all relevant columns, "Whites", "Colors" and "Grays" hide certain columns, and "Various" hides no columns. Changes apply to this worksheet only. Below is the code I have entered in my VBA. All Macros are enabled. What is wrong with it?!

Private Sub Worksheet_Change (ByVal Target As Range)

If Target.Address = "$B$1" Then
If UCase(Target) = "Colors" Then
Columns("E:L").EntireColumn.Hidden = True
Else
Columns("E:L").EntireColumn.Hidden = False
End If
End If


End Sub


Private Sub Worksheet_Change1 (ByVal Target As Range)

If Target.Address = "$B$1" Then
If UCase(Target) = "Grays" Then
Columns("E:H").EntireColumn.Hidden = True
Columns("M:P").EntireColumn.Hidden = True
Else
Columns("E:H").EntireColumn.Hidden = False
Columns("M:P").EntireColumn.Hidden = False
End If
End If


End Sub


Private Sub Worksheet_Change2 (ByVal Target As Range)

If Target.Address = "$B$1" Then
If UCase(Target) = "Whites" Then
Columns("I:P").EntireColumn.Hidden = True
Else
Columns("I:P").EntireColumn.Hidden = False
End If
End If


End Sub


Private Sub Worksheet_Change3 (ByVal Target As Range)

If Target.Address = "$B$1" Then
If UCase(Target) = "Select" Then
Columns("E:P").EntireColumn.Hidden = True
Else
Columns("E:P").EntireColumn.Hidden = False
End If
End If


End Sub

 

[Andrew Poulsom]

You can only have one Worksheet_Change event procedure so all your code needs to go in there. And UCase(Target) can never be "Colors" because that's proper case. You need to test for "COLORS".

 

[Rick Rothstein]

You cannot just make up your own event procedures (your Change1, Change2, etc.)... you must use the ones provided by VB. To do what you want, you need to handle all of the possibilities within VB's Change event. I think this will do what your posted code suggests you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$1" Then
    Columns.Hidden = False
    If UCase(Target) = "COLORS" Then
      Columns("E:L").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "GRAYS" Then
      Columns("E:H").EntireColumn.Hidden = True
      Columns("M:P").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "WHITES" Then
      Columns("I:P").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "SELECT" Then
      Columns("E:P").EntireColumn.Hidden = True
    End If
  End If
End Sub

 

[jscranton]

Also, I think you need to UCASE(Target.Value) = "COLORS"

 

[bruderbell]

*You all beat me to the punch!

Hi,
Give this a try:

Private Sub Worksheet_Change(ByVal Target As Range)
Set KeyCells = Range("B1")

If Not Application.Intersect(KeyCells, Range(Target.Address)) _
           Is Nothing Then

    Columns("E:P").EntireColumn.Hidden = False
    Select Case Target.Value
        Case "Select"
            Columns("E:P").EntireColumn.Hidden = True
        Case "Colors"
            Columns("E:L").EntireColumn.Hidden = True
        Case "Grays"
            Columns("E:H").EntireColumn.Hidden = True
            Columns("M:P").EntireColumn.Hidden = True
        Case "Whites"
            Columns("I:P").EntireColumn.Hidden = True
        End Select
End If
End Sub

Your code was looking for all caps (UCase) when the values in the dropdown were not in all caps. Thus, it didn't match and probably wasn't executing.

 

[bruderbell]

You cannot just make up your own event procedures (your Change1, Change2, etc.)... you must use the ones provided by VB. To do what you want, you need to handle all of the possibilities within VB's Change event. I think this will do what your posted code suggests you want...

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$B$1" Then
    Columns.Hidden = False
    If UCase(Target) = "COLORS" Then
      Columns("E:L").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "GRAYS" Then
      Columns("E:H").EntireColumn.Hidden = True
      Columns("M:P").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "WHITES" Then
      Columns("I:P").EntireColumn.Hidden = True
    ElseIf UCase(Target) = "SELECT" Then
      Columns("E:P").EntireColumn.Hidden = True
    End If
  End If
End Sub

Rick, I consider myself honored to have come up with something so similar to your code! I got my if statement from a M$ KB. Is there any value to all the "If Not ... Is Nothing" stuff?

 

[Rick Rothstein]

Rick, I consider myself honored to have come up with something so similar to your code! I got my if statement from a M$ KB. Is there any value to all the "If Not ... Is Nothing" stuff?

The OP is testing only a single cell for the change, so it is simpler to test if the address of the target cell is that single cell address. When there are more cells to test for changing, then the Intersect method is handier than AND'ing together multiple individual address tests. I like the Select Case structure you proposed... my first thought was to use it in my response, but then decided the If..ElseIf structure was not all that bad for what the OP is doing and I was afraid the OP might not be familiar with Select Case and wanted to avoid a repetitive set of teaching posts trying to introduce him/her to it.