VBA code only works correctly in Debug mode

**tpthatsme** · 02-09-2012, 09:22 AM

Hello Excel Forum, Long time reader, first time poster. I am working on a small project at work and ran into a problem. I duplicated the problem in a very simple worksheet and VBA for troubleshooting. I have a form that populates a worksheet cell with a number (cell A1). This is done with a sppinner (SpinnerButton1) changing the value in a textbox (TextBox1). Another worksheet cell squares the number in A1 (cell B1). That squared number is then displayed on the form in a second text box (TextBox2). All data is displayed correctly in the worksheet, but the squared number on the form is always one calculation behind. Example: First number is spinned up to 3 and squared number on worksheet is 9 but the form displays 4. First number is spinned up again to 4 and squared number on worksheet is 16 but the form displays 9. In Debuging mode, it works as expected. Any ideas? VBA code below. SpinButton1 and TextBox1 ControlSource is 'Sheet1'!A1. B1 has formula "=a1*a1".

Please Login or Register  to view this content.

Thanks for your help!

**OnErrorGoto0** · 02-09-2012, 09:35 AM

Please put your code in code tags, per the forum rules.

**AndyPS** · 02-09-2012, 11:13 AM

I suspect this problem is due to the VBA code running prior to the workbook recalculation.
I managed to get some success by adding a DoEvents statement in the SpinButton change event. This makes the code wait for other events. I also repained the form to ensure changes show up though this doesn't appear to be essential in this case.

Please Login or Register  to view this content.

Hope this helps.

**OnErrorGoto0** · 02-09-2012, 11:22 AM

Is your spinbutton linked directly to a cell? If so, I would suggest using code to update the cell instead.

**snb** · 02-09-2012, 11:36 AM

You don't need the sheet to calculate

Please Login or Register  to view this content.

**tpthatsme** · 02-09-2012, 11:38 AM

Excellent AndyPS!! DoEvents did the trick! Thank you very much for your help!

**AndyPS** · 02-09-2012, 12:51 PM

Jolly good. I'm guessing your trivial example was just to illustrate the problem and your real workbook has complicated calculations dependent on the adjusted cell?
Normally I avoid linking form controls (e.g. the spinbutton) directly to a worksheet cell. If you alter the layout of the worksheet, the form design needs to be modified.
There is more flexibility in having a public property and method in the form code (akin to a class) and using range names to identify the target cell. It is more complex to set up, though.