kath_excel
New Member
- Joined
- Dec 2, 2016
- Messages
- 8
Hello, There are 2 sheets in a file. Sheet 1 name is "Employee Form" and Sheet 2 is "Upload".
On Sheet 1, there is a drop down (in row F18)with 3 items: "T-V", "O-A" and "Please Select from the Drop Down Menu"
On Sheet 2(Upload), there are 2 separate tables. Table 1 for "T-V" and Table 2 for "O-A"
Range for table 1 on sheet 2 is A1:A10 and table 2 is A15:A18
When I select "T-V" from the drop down then only table 1 should be visible and when I select "O-A" then only table 2 should be visible. I have written the below but it is not working. I have done this in the past but I don't know why it is not working this time. Please help.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VT As Range
Dim OC As Range
Dim CT As Range
Set VT= Sheets("Upload").Range("A1:A10")
Set OC= Sheets("Upload").Range("A15:A18")
Set CT= Sheets("Employee Form").Range("F18")
If Not Intersect(Target, Target.Sheet1.Range("F18")) Is Nothing Then
If CT.Value = "T-V" Then
VT.EntireRow.Hidden = False
OffCycle.EntireRow.Hidden = True
ElseIf ChangType.Value = "O-A" Then
VT.EntireRow.Hidden = True
OC.EntireRow.Hidden = False
ElseIf ChangType.Value = "Please Select from the Drop Down Menu" Then
VT.EntireRow.Hidden = True
OC.EntireRow.Hidden = True
End If
End If
End Sub
On Sheet 1, there is a drop down (in row F18)with 3 items: "T-V", "O-A" and "Please Select from the Drop Down Menu"
On Sheet 2(Upload), there are 2 separate tables. Table 1 for "T-V" and Table 2 for "O-A"
Range for table 1 on sheet 2 is A1:A10 and table 2 is A15:A18
When I select "T-V" from the drop down then only table 1 should be visible and when I select "O-A" then only table 2 should be visible. I have written the below but it is not working. I have done this in the past but I don't know why it is not working this time. Please help.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim VT As Range
Dim OC As Range
Dim CT As Range
Set VT= Sheets("Upload").Range("A1:A10")
Set OC= Sheets("Upload").Range("A15:A18")
Set CT= Sheets("Employee Form").Range("F18")
If Not Intersect(Target, Target.Sheet1.Range("F18")) Is Nothing Then
If CT.Value = "T-V" Then
VT.EntireRow.Hidden = False
OffCycle.EntireRow.Hidden = True
ElseIf ChangType.Value = "O-A" Then
VT.EntireRow.Hidden = True
OC.EntireRow.Hidden = False
ElseIf ChangType.Value = "Please Select from the Drop Down Menu" Then
VT.EntireRow.Hidden = True
OC.EntireRow.Hidden = True
End If
End If
End Sub