Cell Range display using Drop Down Selection

[kath_excel]

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

 

[BQardi]

Shouldn't they be CT​ and OC?

[COLOR=#333333]Option Explicit[/COLOR]
[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]
[COLOR=#333333]Dim VT As Range[/COLOR]
[COLOR=#333333]Dim OC As Range[/COLOR]
[COLOR=#333333]Dim CT As Range[/COLOR]

[COLOR=#333333]Set VT= Sheets("Upload").Range("A1:A10")[/COLOR]
[COLOR=#333333]Set OC= Sheets("Upload").Range("A15:A18")[/COLOR]
[COLOR=#333333]Set CT= Sheets("Employee Form").Range("F18")[/COLOR]

[COLOR=#333333]If Not Intersect(Target, [/COLOR][B][COLOR=#008000]Target.[/COLOR][/B][COLOR=#333333]Sheet1.Range("F18")) Is Nothing Then [/COLOR][COLOR=#008000]'No need for [B]Target [/B]here[/COLOR]
[COLOR=#333333]If CT.Value = "T-V" Then[/COLOR]
[COLOR=#333333]VT.EntireRow.Hidden = False[/COLOR]
[COLOR=#0000ff][B]OffCycle[/B][/COLOR][COLOR=#333333].EntireRow.Hidden = True[/COLOR]

[COLOR=#333333]ElseIf [/COLOR][COLOR=#ff0000][B]ChangType[/B][/COLOR][COLOR=#333333].Value = "O-A" Then[/COLOR]
[COLOR=#333333]VT.EntireRow.Hidden = True[/COLOR]
[COLOR=#333333]OC.EntireRow.Hidden = False[/COLOR]

[COLOR=#333333]ElseIf [/COLOR][COLOR=#ff0000][B]ChangType[/B][/COLOR][COLOR=#333333].Value = "Please Select from the Drop Down Menu" Then[/COLOR]
[COLOR=#333333]VT.EntireRow.Hidden = True[/COLOR]
[COLOR=#333333]OC.EntireRow.Hidden = True[/COLOR]

[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]End Sub[/COLOR]

 

[JoeMo]

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

In addition to the comments in post #2, remove the text shown in bold red font above.

 

[kath_excel]

It is still not working. Could other macros in the file be a reason why this wont work?

 

[BQardi]

I just tried testing how dropdowns work when connected to cells, and realized that they (both ActiveX and form controls) won't activate Worksheet_Change events at all...
A better way to accomplish what you want, would be to have you assign a macro to your dropdown...
What type is your dropdown (combobox)?
For ActiveX: Go to Design mode and double click the control (the dropdown) the create/paste the code in there
For form control: Right click the control and select Assign macro...

 

[kath_excel]

The dropdown was created using a list through data validation. No form or ActiveX controls were used. How can I solve this problem without changing the drop down?

 

[JoeMo]

The dropdown was created using a list through data validation. No form or ActiveX controls were used. How can I solve this problem without changing the drop down?

Can you post the revised code you are using now that does not work and elaborate on exactly what happens (or doesn't happen) when you make a change using the validation drop down?

 

[BQardi]

Data validation is fine, then it is something else...

I can make it work with this:

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, OC) Is Nothing Then
    If CT.Value = "T-V" Then
        VT.EntireRow.Hidden = False
        OC.EntireRow.Hidden = True
    ElseIf CT.Value = "O-A" Then
        VT.EntireRow.Hidden = True
        OC.EntireRow.Hidden = False
    ElseIf CT.Value = "Please Select from the Drop Down Menu" Then
        VT.EntireRow.Hidden = True
        OC.EntireRow.Hidden = True
    End If
End If
End Sub