Dropdown menus on separate sheets mirroring each other

[mark9988]

Hello,

I have identical dropdown validation menus on 4 separate sheets. I would like for them to mirror each other, so that:

If I select "Hot" from the dropdown in sheet 1, it then it automatically changes to "Hot" across the dropdowns in remaining sheets (sheet 2, 3, 4).

or

If I select "Cold" from the dropdown in sheet 3, it then it automatically changes to "Cold" across the dropdowns in remaining sheets (sheet 1,2,4).


Best,
Mark9988

 

[mark9988]

So I did some research on my end and found a partial solution, but it only works for 2 sheets.

Can someone please help modify the macros below so it works for 4 sheets?

Many Thanks!!

Sheet 1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Range("A1")
Set r2 = Sheets("Sheet2").Range("A1")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r1.Value
Application.EnableEvents = True
End Sub


Sheet 2
Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Range("A1")
Set r2 = Sheets("Sheet1").Range("A1")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r1.Value
Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

Try putting this script in all 5 sheets

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/16/2022  5:31:14 PM  EST
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Sheets(1).Range("A1").Value = Target.Value
Sheets(2).Range("A1").Value = Target.Value
Sheets(3).Range("A1").Value = Target.Value
Sheets(4).Range("A1").Value = Target.Value
Sheets(5).Range("A1").Value = Target.Value
Application.EnableEvents = True
End If
End Sub

 

[mark9988]

Works great, Thank you!!

I just have one follow-up. My actual file does not have the dropdowns residing in cell A1 for all the sheets (I used A1 for illustrative purposes).

Any chance you can modify this macro to account for these dropdowns being in different cells?

For example - here are the actual cell locations of the dropdowns:

Sheets(1): D5
Sheets(2): B6
Sheets(3): E10
Sheets(4): A4

Best,
Mark9988

Try putting this script in all 5 sheets

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/16/2022  5:31:14 PM  EST
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Sheets(1).Range("A1").Value = Target.Value
Sheets(2).Range("A1").Value = Target.Value
Sheets(3).Range("A1").Value = Target.Value
Sheets(4).Range("A1").Value = Target.Value
Sheets(5).Range("A1").Value = Target.Value
Application.EnableEvents = True
End If
End Sub

 

[My Aswer Is This]

Just change the code:
Sheets(1).Range("D5").Value = Target.Value
I think that would work.

 

[My Aswer Is This]

And on each sheet you would need to change this:
If Target.Address = "$A$1" Then
To If Target.Address = "$G$4" Then

Change as needed.

 

[mark9988]

And on each sheet you would need to change this:
If Target.Address = "$A$1" Then
To If Target.Address = "$G$4" Then

Change as needed.

Excellent - Works great, thank you!!

One additional follow-up.....is it possible to tweak the macro to allow for two validation boxes in the same worksheet?

Best,
Mark9988

 

[My Aswer Is This]

Having more the one is possible.
You should see how my script works and be able to modify scripts to your needs.
Not really sure what is happening when you select the value in the Data validation list.
In the sheet range change script you would have to show me what you want to happen when the value is selected.

For example: If Target.value ="Apple" then Sheets("Apple").Range("C2").value="Pie"

 

[My Aswer Is This]

What is you script doing other the sending same value to all sheets?
Show me the script you're now using.

 

[My Aswer Is This]

Here is a example of having more the one.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/17/2022  8:29:15 AM  EST
On Error GoTo M
If Target.Address = "$C$12" Then Sheets("Alpha").Range("C12").Value = Target.Value
If Target.Address = "$D$12" Then Sheets("Alpha").Range("D12").Value = Target.Value

Exit Sub
M:
MsgBox "We had a problem"
End Sub