Hello,

I am wondering if anyone knows a visual basic method to update the
values of two cells in visual basic so one is equal to the other if it
changes.

For example, if I change A1 to be "5" I want C1 to be updated to "5"
_and_ if I change C1 to be "5" I want A1 to be updated to "5" as well.

I've been searching for a cell(s).change option but was not able to
find that function, does anyone have any suggestions or guesses?

Thanks everyone for your help!

Shaun

Hi Shaun,

Try this in the Worksheet module (right click the sheet tab View code)

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errH
If Not Intersect(Target, Range("A1,C1")) Is Nothing Then
Application.EnableEvents = False
If Target(1).Address = "$A$1" Then
Range("C1").Value = Range("A1").Value
Else
Range("A1").Value = Range("C1").Value
End If
End If

errH:
Application.EnableEvents = True
End

Regards,
Peter T

"Shaun" wrote in message
oups.com...
Hello,

I am wondering if anyone knows a visual basic method to update the
values of two cells in visual basic so one is equal to the other if it
changes.

For example, if I change A1 to be "5" I want C1 to be updated to "5"
_and_ if I change C1 to be "5" I want A1 to be updated to "5" as well.

I've been searching for a cell(s).change option but was not able to
find that function, does anyone have any suggestions or guesses?

Thanks everyone for your help!

Shaun

Right click on the sheet tab and paste in code like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count 1 Then Exit Sub
On Error GoTo ErrHandler
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("C1") = Target.Value
ElseIf Target.Address = "$C$1" Then
Application.EnableEvents = False
Range("A1").Value = Target.Value
End If
ErrHandler:
Application.EnableEvents = True

End Sub

for general information on Excel Events see Chip Pearson's site
http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"Shaun" wrote in message
oups.com...
Hello,

I am wondering if anyone knows a visual basic method to update the
values of two cells in visual basic so one is equal to the other if it
changes.

For example, if I change A1 to be "5" I want C1 to be updated to "5"
_and_ if I change C1 to be "5" I want A1 to be updated to "5" as well.

I've been searching for a cell(s).change option but was not able to
find that function, does anyone have any suggestions or guesses?

Thanks everyone for your help!

Shaun

This code needs to be pasted into the sheet you want to use it on, not into a
general module...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Errorhandler
Application.EnableEvents = False
If Target.Address = "$A$1" Then
Range("C1").Value = Target.Value
ElseIf Target.Address = "$C$1" Then
Range("A1").Value = Target.Value
End If
Errorhandler:
Application.EnableEvents = True
End Sub

--
HTH...

Jim Thomlinson


"Shaun" wrote:

Hello,

I am wondering if anyone knows a visual basic method to update the
values of two cells in visual basic so one is equal to the other if it
changes.

For example, if I change A1 to be "5" I want C1 to be updated to "5"
_and_ if I change C1 to be "5" I want A1 to be updated to "5" as well.

I've been searching for a cell(s).change option but was not able to
find that function, does anyone have any suggestions or guesses?

Thanks everyone for your help!

Shaun

Hi,

Thanks to Peter T, Tom Ogilvy & Jim Thomlinson for your replies!

All three methods work :D! (Peter T's needs End Sub instead of End and
then it works perfect).

It is very neat to see it autoupdate, this is very useful. I can have a
master input sheet that has updatable figures now.

Bye,

Shaun

Tom's code is probably the best of the bunch (I should have done a count of
the target cells as Tom's code does.)... I would go with that. It may be
important to note that values pasted into A1 or C1 will not trigger the
macro. Just a warning.
--
HTH...

Jim Thomlinson


"Shaun" wrote:

Hi,

Thanks to Peter T, Tom Ogilvy & Jim Thomlinson for your replies!

All three methods work :D! (Peter T's needs End Sub instead of End and
then it works perfect).

It is very neat to see it autoupdate, this is very useful. I can have a
master input sheet that has updatable figures now.

Bye,

Shaun

Just some added info:

In xl2003 at least, copying a single cell to either A1 or C1 caused the
change event to run.

with my code, copying a multicell range to a destination that included A1 or
C1 would cause it not to run because of the If Target.count 1 then exit
sub in my code.

It really depends on what you want to account for whether that line is
useful or not.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in
message ...
Tom's code is probably the best of the bunch (I should have done a count
of
the target cells as Tom's code does.)... I would go with that. It may be
important to note that values pasted into A1 or C1 will not trigger the
macro. Just a warning.
--
HTH...

Jim Thomlinson


"Shaun" wrote:

Hi,

Thanks to Peter T, Tom Ogilvy & Jim Thomlinson for your replies!

All three methods work :D! (Peter T's needs End Sub instead of End and
then it works perfect).

It is very neat to see it autoupdate, this is very useful. I can have a
master input sheet that has updatable figures now.

Bye,

Shaun

In xl2003 at least, copying a single cell to either A1 or C1 caused the
change event to run.

Similarly in my xl2000, also Cut

None of the three examples catered for all situations of how a cell might be
changed, particularly if changing multiple cells, eg pasting or deleting
(incl Cut) and the first selected cell (ie activecell) is not A1 or C1. Mine
catered perhaps for more scenarios using Target(1) but only because the
cells in question were in row 1.

Following attempts to equalise B & D in rows 2-20 even if the active cell is
not in these ranges at time of change and/or multiple cells changed in one
go, overkill !

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Rng As Range, c As Long

' On Error Resume Next
Set Rng = Intersect(Target, Range("B2:B20"))

If Not Rng Is Nothing Then
c = 2
Else
Set Rng = Intersect(Target, Range("D2:D20"))
If Not Rng Is Nothing Then
c = -2
End If
End If

If c Then
On Error GoTo ErrH
Application.EnableEvents = False
Rng.Offset(0, c).Value = Rng.Value
End If

ErrH:
Application.EnableEvents = True
End Sub

Shaun - for your particular purposes change the ranges to "A1" & "C1"
respectively

Regards,
Peter T

PS hope I've included "Sub" after "End" this time !


"Tom Ogilvy" wrote in message
...
Just some added info:

In xl2003 at least, copying a single cell to either A1 or C1 caused the
change event to run.

with my code, copying a multicell range to a destination that included A1
or
C1 would cause it not to run because of the If Target.count 1 then exit
sub in my code.

It really depends on what you want to account for whether that line is
useful or not.

--
Regards,
Tom Ogilvy


"Jim Thomlinson" wrote in
message ...
Tom's code is probably the best of the bunch (I should have done a count
of
the target cells as Tom's code does.)... I would go with that. It may be
important to note that values pasted into A1 or C1 will not trigger the
macro. Just a warning.
--
HTH...

Jim Thomlinson


"Shaun" wrote:

Hi,

Thanks to Peter T, Tom Ogilvy & Jim Thomlinson for your replies!

All three methods work :D! (Peter T's needs End Sub instead of End and
then it works perfect).

It is very neat to see it autoupdate, this is very useful. I can have
a
master input sheet that has updatable figures now.

Bye,

Shaun