can a cell be both input and output?

[bob]

Is it possible to do this:

let's call cell A1 "inches", and B1 "cm"

if the user enters a number in A1 (then hit tab or enter), the equivalent
length in cm will be displayed in B1

if the user enters a number in B1, the equivalent length in inches will be
displayed in A1

Obviously one cannot store the forumulas in either A1 or B1 because the
formulas would get erased when the user enters a number

 

[Claus Busch]

Hi Bob,

Am Thu, 14 Nov 2013 09:02:03 -0800 schrieb bob:

let's call cell A1 "inches", and B1 "cm"

if the user enters a number in A1 (then hit tab or enter), the equivalent
length in cm will be displayed in B1

if the user enters a number in B1, the equivalent length in inches will be
displayed in A1

that is possible using VBA. Right click on sheet tab => Show code =>
Paste following code into the code module of the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Address(0, 0)
Case "A1"
Range("B1") = 2.54 * Target
Case "B1"
Range("A1") = Target / 2.54
End Select
End Sub


Regards
Claus B.

 

[Stan Brown]

Is it possible to do this:

if the user enters a number in A1 (then hit tab or enter), the equivalent
length in cm will be displayed in B1

if the user enters a number in B1, the equivalent length in inches will be
displayed in A1

It's possible, but you need to write Visual Basic for Applications
code to process the "Change" event.

I don't see a straightforward way to do it purely in worksheet
formulas.

 

[Claus Busch]

Hi Howard,

Am Thu, 14 Nov 2013 17:55:28 -0800 (PST) schrieb Howard Silcock:

I have now played with this and found a small problem. Each time the Worksheet_Change macro enters the new value in a cell, this is detected as a new change and the macro is launched again, this time changing the value in the other cell, and so on. When I tracked the process, it stopped after 95 iterations.

I noticed it because I accidentally typed a slightly incorrect value in place of 2.54 in one of the statements in the code, so the second conversion no longer changed the first cell back to its original value again. This made the values in A1 and B1 oscillate around for a while before settling down to two values, neither of them equal to the value that should be there. Normally you probably wouldn't notice this happening as the cells are always overwritten with the same values over and over.

Is there some way of preventing calls to the Worksheet_Change macro being initiated from within the macro itself?

try:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
On Error GoTo ERRHANDLER
Select Case Target.Address(0, 0)
Case "A1"
Range("B1") = 2.54 * Target
Case "B1"
Range("A1") = Target / 2.54
End Select
ERRHANDLER:
Application.EnableEvents = True
End Sub


Regards
Claus B.