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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Friday, 15 November 2013 04:12:01 UTC+11, Claus Busch wrote:
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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

This is really neat. I have often wanted to do this kind of thing but it seemed to be "obviously impossible", so I never even explored the options.

Howard

On Thu, 14 Nov 2013 09:02:03 -0800, bob wrote:
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.



--
Stan Brown, Oak Road Systems, Tompkins County, New York, USA
http://OakRoadSystems.com
Shikata ga nai...

On Friday, 15 November 2013 07:41:52 UTC+11, Howard Silcock wrote:
On Friday, 15 November 2013 04:12:01 UTC+11, Claus Busch wrote:

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.



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2



This is really neat. I have often wanted to do this kind of thing but it seemed to be "obviously impossible", so I never even explored the options.



Howard

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?

Howard

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Friday, 15 November 2013 17:37:12 UTC+11, Claus Busch wrote:
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.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Yes, that works. Thank you.

Howard