Creating two fields always equal to each other

B

blindbug

New Member
Joined
Jun 9, 2006
Messages
14
I have (2) worksheets, each have a cell in them that say 'Month:' with a dropdown validation list of the months available. I would like to be able to do the following:

Sheet 1, Cell A2: Hit dropdown and change to 'November'
- Update Sheet 2, Cell A2 to say 'November'

Sheet 2, Cell A2: Hit dropdown and change to 'December'
- Update Sheet 1, Cell A2 to say 'December'

As you can see, I would like to have each cell be updatable, but when you update one of the cells, it will automatically update the other. I had some VBA that was doing something similar, but when I ran it I was basically running into an infinite loop problem.

Any suggestions?
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi blindbug

You have to use the Change Event of the respective sheet to update the other

Paste in Sheet1's module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address <> "$A$2" Then Exit Sub
 
Application.EnableEvents = False
Worksheets("Sheet2").Range("A2").Value = Range("A2").Value
Application.EnableEvents = True
End Sub

Paste in Sheet2's module:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address <> "$A$2" Then Exit Sub
 
Application.EnableEvents = False
Worksheets("Sheet1").Range("A2").Value = Range("A2").Value
Application.EnableEvents = True
End Sub
 
Upvote 0
Thanks, I see what I was missing when I was doing the VBA before. One question though.

When using named ranges, is it possible, programatically, to do the following? Assuming that MonthDropdown1 is on Sheet 1 and MonthDropdown2 is on sheet 2.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address <> "MonthDropdown1" Then Exit Sub
 
Application.EnableEvents = False
Range("MonthDropdown2").Value = Range("MonthDropdown1").Value
Application.EnableEvents = True
End Sub
 
Upvote 0
Hi again

If these were General Modules you'd have no problem. However, these are Worksheet Modules.

The vba references in a Worksheet Module refer implicitly to that worksheet. If you want to refer to another worksheet you have to make it explicit. That's why you'll get an error id you try to use directly Range("MonthDropDown2") in Worksheet Module Sheet1.

Notice that there's no problem with Range("MonthDropDown1") in Worksheet Module Sheet1 since the Name refers to this worksheet.

There are several ways to do it. 2 examples

1 - The direct one is to parse the name reference and use the usual Worksheets().Range() notation.

Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim vCellAddr
 
If Target.Address <> Range("MonthDropDown1").Address Then Exit Sub
 
vCellAddr = Split(Mid(ActiveWorkbook.Names("MonthDropDown2").RefersTo, 2), "!")
Application.EnableEvents = False
Worksheets(vCellAddr(0)).Range(vCellAddr(1)).Value = Range("MonthDropDown1").Value
Application.EnableEvents = True
End Sub

2 - A less obvious one but more compact is to use the fact that the RefersTo property of a name in the case of a named range gives you a worksheet reference (like =Sheet2!A2). You can use this fact to evaluate it to a vba range.

Try:
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
 
If Target.Address <> Range("MonthDropDown1").Address Then Exit Sub
 
Application.EnableEvents = False
Evaluate(ActiveWorkbook.Names("MonthDropDown2").RefersTo).Value = Range("MonthDropDown1")
Application.EnableEvents = True
End Sub

Of course the same applies to the Event Procedure of Sheet2.

Remark: You also has a simpler syntax error comparing the addresses of the target and the name.

If Target.Address <> "MonthDropdown1" Then Exit Sub

could never work. Target.Address is something like "$A$2", nothing like the string "MonthDropdown1"

The correct statement is:

If Target.Address <> Range("MonthDropDown1").Address Then Exit Sub

where the second part of the inequality gets the address from the named range.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Dynamic chart based on month
Replies
7
Views
316
skillilea
S
R
  • Question
VBA Loop Through Data Validation Drop Down List
Replies
5
Views
355
rlv01
R
W
  • Question
Sheet that updates by referencing cells in workbooks located in separate folders on shared drive
Replies
0
Views
150
Woodpusher147
W
A
  • Solved
Formula for subtracting from a total, if a specific item is selected from a dropdown
2
Replies
13
Views
133
mumps
mumps
D
  • Question
Dropdown list that is not data validation
Replies
1
Views
632
delaney1102
D

Forum statistics

Threads
1,214,897
Messages
6,122,151
Members
449,068
Latest member
shiz11713

Share this page

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
Back
Top