Two Way Cell Value Connection A1->C1 and C1->A1

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Shaun - 24 Jan 2006 19:50 GMT

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

Reply to this Message

Peter T - 24 Jan 2006 20:15 GMT

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

> Hello,
>
[quoted text clipped - 11 lines]
>
> Shaun

Reply to this Message

Tom Ogilvy - 24 Jan 2006 20:17 GMT

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

Signature

Regards,
Tom Ogilvy

> Hello,
>
[quoted text clipped - 11 lines]
>
> Shaun

Reply to this Message

Jim Thomlinson - 24 Jan 2006 20:20 GMT

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

Signature

HTH...

Jim Thomlinson

> Hello,
>
[quoted text clipped - 11 lines]
>
> Shaun

Reply to this Message

Shaun - 24 Jan 2006 21:03 GMT

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

Reply to this Message

Jim Thomlinson - 24 Jan 2006 21:35 GMT

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.

Signature

HTH...

Jim Thomlinson

> Hi,
>
[quoted text clipped - 9 lines]
>
> Shaun

Reply to this Message

Tom Ogilvy - 24 Jan 2006 22:12 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 14 lines]
> >
> > Shaun

Reply to this Message

Peter T - 24 Jan 2006 23:47 GMT

> 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 !

> Just some added info:
>
[quoted text clipped - 35 lines]
> > >
> > > Shaun

Reply to this Message