Hiding/Showing Columns Based on Drop-Down List - VBA

[rubenidas]

Hi All,
I have been struggling to get this working. I am trying to hide columns (C thru AA is the range) based on the value selected from the drop down in cell A12. The values are something like "Show All Details", "Show Financials", "Show KPI Results".... etc. For Example, i want to hide Columns B:O (B thru O) if "SHow KPI Results" is selected.

Your help is greatly appreciated!!

 Private Sub Worksheet_Change(ByVal Target As Range)Dim rTargetcell As Excel.Range


    Set rTargetcell = Range("A12")
If rTargetcell Is blank Then
Columns("B:AA").EntireColumn.Hidden = False
Else


Select Case Range("A12").Value


    Case "Show KPI Results"
      Columns("B:O").EntireColumn.Hidden = True


    Case "Show Financials"
      Columns("P").EntireColumn.Hidden = True
      Columns("R").EntireColumn.Hidden = True
    
End Select
End If
End Sub

 

[lrobbo314]

This seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then
    ActiveSheet.Columns.Hidden = False
    Select Case Target.Value
        Case "Show KPI Results"
          Columns("B:O").EntireColumn.Hidden = True
    
    
        Case "Show Financials"
          Columns("P").EntireColumn.Hidden = True
          Columns("R").EntireColumn.Hidden = True
    End Select
End If
End Sub

 

[rubenidas]

Thanks! it worked.. i figured out i have to put the code into the sheet itself...not under Module.. Darn it! all this time i couldn't figure out why it was not working for me.. haha

This seems to work.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A12")) Is Nothing Then
    ActiveSheet.Columns.Hidden = False
    Select Case Target.Value
        Case "Show KPI Results"
          Columns("B:O").EntireColumn.Hidden = True
    
    
        Case "Show Financials"
          Columns("P").EntireColumn.Hidden = True
          Columns("R").EntireColumn.Hidden = True
    End Select
End If
End Sub