Cell Range display using Drop Down Selection

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
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483
Shouldn't they be CT​ and OC?
Code:
[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]
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Dec 2, 2016
Messages
8
It is still not working. Could other macros in the file be a reason why this wont work?
 

BQardi

Active Member
Joined
Aug 30, 2016
Messages
483

ADVERTISEMENT

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

New Member
Joined
Dec 2, 2016
Messages
8
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,435
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

Active Member
Joined
Aug 30, 2016
Messages
483
Data validation is fine, then it is something else...

I can make it work with this:
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,130,342
Messages
5,641,592
Members
417,224
Latest member
llama9207

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top