VBA Select Filter 2nd Drop Down List to have the same Value as the 1st

eazy899

New Member
Joined
May 17, 2016
Messages
12
Hi all,

I have a Drop Down List in Sheet 1, Cell F2 that has the values 1 to 5 in it for selection.
I have a 2nd Drop Down in Sheet 2, Cell F2 that also has the values 1 to 5 in it for selection.

Both Drop Downs use the same List for Data Validation.

I need to be able to filter the Drop Down on Sheet 1 for a value and have that same value applied to the dropdown in Sheet 2.

I previously did something similar with the below but cannot remember how for the life of me.

Private Sub Worksheet_Change(ByVal Target As Range)

Any help would be appreciated. Thanks.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
When dropdowns in cel A1:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Sheet2.[A1] = Sheets1.[A1]
End Sub
 

eazy899

New Member
Joined
May 17, 2016
Messages
12
When dropdowns in cel A1:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then Sheet2.[A1] = Sheets1.[A1]
End Sub
Thank you.

This is very helpful and worked when I wrote the code out as below but unfortunately resulted in another issue.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim xWs As Worksheet

If Target.Address = "$G$2" Then


Worksheets("Data Sheet").Range("D2") = Target
Worksheets("Directorate Summary").Range("G2") = Target

End If

End Sub

The other issue is that:
1. I have 3 Sheets with 3 Drop Down Lists
2. Whenever I change the value in Sheet 1, I need the Value in Sheet 2 and 3 to match.
3. The above code enables me to change the value in Sheet 1 resulting in the same Value in Sheet 2 and 3.
4. However, when I put the same code into Sheet 2 or Sheet 3, it results in an error.


I.e. If the Macro in Sheet 1 changes the value in Sheet 2 and I have the same macro in Sheet 2, when I change the value in Sheet 2, I also need it to change the value in Sheet 1 but this activates the Macro in Sheet 1 by changing its value causing an error.

How do I rectify this so that changing the value in any of the 3 sheets results in the same value in the other 2 sheets?
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
The Worksheet_Change event do you need on all the three sheets.
 

eazy899

New Member
Joined
May 17, 2016
Messages
12

ADVERTISEMENT

The Worksheet_Change event do you need on all the three sheets.
Yes, I need all 3 sheets.

All 3 sheets have a Drop Down List for Months 1 to 12.
I need the value in all 3 sheets to be the same no matter which sheet is used for the value.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
Copy the macro to the other two sheets and replace the sheetname.
 

eazy899

New Member
Joined
May 17, 2016
Messages
12

ADVERTISEMENT

Copy the macro to the other two sheets and replace the sheetname.
When I try that, it results in the error that causes Excel to crash.

I.e. When Sheet 1's Value Changes, it changes Sheet 2's value which is fine but then if Sheet 2 Value's change then it triggers the macro on Sheet 2 to make the Sheet 1 Value change.
That loop causes the crash.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,091
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xWs As Worksheet
If Target.Address = "$G$2" Then
     Application.EnableEvents = False
     Worksheets("Data Sheet").Range("D2") = Target
     Worksheets("Directorate Summary").Range("G2") = Target
     Application.EnableEvents = True
End If
End Sub
 
Solution

eazy899

New Member
Joined
May 17, 2016
Messages
12
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim xWs As Worksheet
If Target.Address = "$G$2" Then
     Application.EnableEvents = False
     Worksheets("Data Sheet").Range("D2") = Target
     Worksheets("Directorate Summary").Range("G2") = Target
     Application.EnableEvents = True
End If
End Sub
This works. Thank you so much.

Really appreciate the help.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,051
Messages
5,639,773
Members
417,112
Latest member
PachRedoc

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