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