View Single Post
 
Old 04-15-2015, 08:40 AM
Silver1379 Silver1379 is offline Windows 8 Office 2010 32bit
Novice
 
Join Date: Apr 2015
Posts: 1
Silver1379 is on a distinguished road
Default VBA Code to Hide columns based on selection on prior sheet

Hi, I am working on a vba code that hides columns on tab 2 based on a section in tab 1. The code I have is below, only the CCB Law - Mortgage Banking section works as it should. Any help would be greatly appreciated!
What I need the code to do is, if the user selects CCB Non Law from the drop down on tab 1, then hide columns D:E on tab 2
If the User selects CCB Law- CLLRT then it hides Column E
If the user selects CCB Law - Mortgage Banking the it hides Column D

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveSheet
Sheets(2).Unprotect Password:="SoraAX14"
 If Target.Address = "$C$42" Then
    If Target.Value = "CCB Non Law" Then
      Sheets(2).Columns("D:E").EntireColumn.Hidden = True
        End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Non Law" Then
    Sheets(2).Columns("D:E").EntireColumn.Hidden = False
       End If
    End If
  If Target.Address = "$C$42" Then
    If Target.Value = "CCB Law - Mortgage Banking" Then
      Sheets(2).Columns("D:D").EntireColumn.Hidden = True
     End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Law - Mortgage Banking" Then
    Sheets(2).Columns("D:D").EntireColumn.Hidden = False
    End If
    End If
      If Target.Address = "$C$42" Then
    If Target.Value = "CCB Law- CLLRT" Then
      Sheets(2).Columns("E:E").EntireColumn.Hidden = True
     End If
    End If
   If Target.Address = "$C$42" Then
   If Target.Value <> "CCB Law- CLLRT" Then
    Sheets(2).Columns("E:E").EntireColumn.Hidden = False
    End If
       Sheets(2).Protect Password:="SoraAX14"
     End If
    
     End With
End Sub
This is the only piece of the code that is working as it should:
If Target.Address = "$C$42" Then
If Target.Value = "CCB Law - Mortgage Banking" Then
Sheets(2).Columns("D").EntireColumn.Hidden = True
End If
End If
If Target.Address = "$C$42" Then
If Target.Value <> "CCB Law - Mortgage Banking" Then
Sheets(2).Columns("D").EntireColumn.Hidden = False
End If
End If

I would appreciate any and all help with this.
Reply With Quote