|
Dual input cells
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 |
Dual input cells
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 |
Dual input cells
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" wrote in message ... 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 |
Dual input cells
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" wrote in message ... 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 |
Dual input cells
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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 01:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com