Dual input cells

[Henrik]

Is it possible to have two input cells more or less 'alternating' so that
each cell assumes the most recent value in either cell?

An example to illustrate: the two cells start empty. A value is entered in
either of the two cells, and both cells now have that value. Aother value is
entered in either cell, and the two cells now have that value.

Ideally I'd like to avoid using VBA. Any ideas?

Henrik

[Stefi]

There is no way to do this without a Change event.
Assuming your two cells are A1 and B1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(False, False) = "A1" Then
Range("B1") = Target.Value
ElseIf Target.Address(False, False) = "B1" Then
Range("A1") = Target.Value
End If
End Sub

Regards,
Stefi

„Henrik” ezt �*rta:

> Is it possible to have two input cells more or less 'alternating' so that
> each cell assumes the most recent value in either cell?
>
> An example to illustrate: the two cells start empty. A value is entered in
> either of the two cells, and both cells now have that value. Aother value is
> entered in either cell, and the two cells now have that value.
>
> Ideally I'd like to avoid using VBA. Any ideas?
>
> Henrik

[Chip Pearson]

You'll want to disable events in this procedure. Otherwise,
changing one cell will call _Change, which changes a cell, which
calls _Change, which changes a cell, which calls _Change, and on
and on and on.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address(False, False) = "A1" Then
Range("B1") = Target.Value
ElseIf Target.Address(False, False) = "B1" Then
Range("A1") = Target.Value
End If
Application.EnableEvents = True
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Stefi" <[email protected]> wrote in message
news:[email protected]...
> There is no way to do this without a Change event.
> Assuming your two cells are A1 and B1:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Address(False, False) = "A1" Then
> Range("B1") = Target.Value
> ElseIf Target.Address(False, False) = "B1" Then
> Range("A1") = Target.Value
> End If
> End Sub
>
> Regards,
> Stefi
>
> "Henrik" ezt �rta:
>
>> Is it possible to have two input cells more or less
>> 'alternating' so that
>> each cell assumes the most recent value in either cell?
>>
>> An example to illustrate: the two cells start empty. A value
>> is entered in
>> either of the two cells, and both cells now have that value.
>> Aother value is
>> entered in either cell, and the two cells now have that value.
>>
>> Ideally I'd like to avoid using VBA. Any ideas?
>>
>> Henrik

[Stefi]

Yes, thanks for the warning, I know it but I always forgot to apply until I
experience the endless loop!

Regards,
Stefi


„Chip Pearson” ezt �*rta:

> You'll want to disable events in this procedure. Otherwise,
> changing one cell will call _Change, which changes a cell, which
> calls _Change, which changes a cell, which calls _Change, and on
> and on and on.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> Application.EnableEvents = False
> If Target.Address(False, False) = "A1" Then
> Range("B1") = Target.Value
> ElseIf Target.Address(False, False) = "B1" Then
> Range("A1") = Target.Value
> End If
> Application.EnableEvents = True
> End Sub
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "Stefi" <[email protected]> wrote in message
> news:[email protected]...
> > There is no way to do this without a Change event.
> > Assuming your two cells are A1 and B1:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Address(False, False) = "A1" Then
> > Range("B1") = Target.Value
> > ElseIf Target.Address(False, False) = "B1" Then
> > Range("A1") = Target.Value
> > End If
> > End Sub
> >
> > Regards,
> > Stefi
> >
> > "Henrik" ezt �*rta:
> >
> >> Is it possible to have two input cells more or less
> >> 'alternating' so that
> >> each cell assumes the most recent value in either cell?
> >>
> >> An example to illustrate: the two cells start empty. A value
> >> is entered in
> >> either of the two cells, and both cells now have that value.
> >> Aother value is
> >> entered in either cell, and the two cells now have that value.
> >>
> >> Ideally I'd like to avoid using VBA. Any ideas?
> >>
> >> Henrik
>
>
>

[Steve Dalton]

A small point, but I would also name the inputs and use the Intersect method
to pick up the event, so that your VB code is nto going to break when you
move stuff around

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

If Not Intersect(Target, Range("Input1") Is Nothing Then
Range("Input2") = Target.Value
ElseIf Not Intersect(Target, Range("Input2") Is Nothing Then
Range("Input1") = Target.Value
End If

Application.EnableEvents = True
End Sub

Regards

Steve Dalton

"Stefi" <[email protected]> wrote in message
news:[email protected]...
> Yes, thanks for the warning, I know it but I always forgot to apply until
I
> experience the endless loop!
>
> Regards,
> Stefi
>
>
> "Chip Pearson" ezt �rta:
>
> > You'll want to disable events in this procedure. Otherwise,
> > changing one cell will call _Change, which changes a cell, which
> > calls _Change, which changes a cell, which calls _Change, and on
> > and on and on.
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > Application.EnableEvents = False
> > If Target.Address(False, False) = "A1" Then
> > Range("B1") = Target.Value
> > ElseIf Target.Address(False, False) = "B1" Then
> > Range("A1") = Target.Value
> > End If
> > Application.EnableEvents = True
> > End Sub
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "Stefi" <[email protected]> wrote in message
> > news:[email protected]...
> > > There is no way to do this without a Change event.
> > > Assuming your two cells are A1 and B1:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Address(False, False) = "A1" Then
> > > Range("B1") = Target.Value
> > > ElseIf Target.Address(False, False) = "B1" Then
> > > Range("A1") = Target.Value
> > > End If
> > > End Sub
> > >
> > > Regards,
> > > Stefi
> > >
> > > "Henrik" ezt �rta:
> > >
> > >> Is it possible to have two input cells more or less
> > >> 'alternating' so that
> > >> each cell assumes the most recent value in either cell?
> > >>
> > >> An example to illustrate: the two cells start empty. A value
> > >> is entered in
> > >> either of the two cells, and both cells now have that value.
> > >> Aother value is
> > >> entered in either cell, and the two cells now have that value.
> > >>
> > >> Ideally I'd like to avoid using VBA. Any ideas?
> > >>
> > >> Henrik
> >
> >
> >