| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Setting of input cells as blue font and formula cells as black fon | Excel Discussion (Misc queries) | |||
| Trying to Capitalise on Input to Cells | Excel Worksheet Functions | |||
| how can i make dual label cells? | Excel Worksheet Functions | |||
| Input Cells | Excel Worksheet Functions | |||
| Switch cells on input | Excel Programming | |||
Linear Mode
