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".

Private Sub SpinButton1_Change()
TextBox1.Text = SpinButton1.Value
TextBox2.Text = Range("b1").Value
End Sub

Private Sub UserForm_Activate()
TextBox2.Value = Range("b1").Value
End Sub

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.

Private Sub SpinButton1_Change()
    TextBox1.Text = SpinButton1.Value
    
    DoEvents
    
    TextBox2.Text = Worksheets("Sheet1").Range("B1").Value
    UserForm1.Repaint
End Sub

Private Sub UserForm_Activate()
    TextBox2.Value = Range("b1").Value
    Me.Repaint

End Sub

Hope this helps.

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

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

**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

Private Sub SpinButton1_Change()
  with SpinButton1
    TextBox1.Text =.value
    TextBox2.Text =.value ^2
  end with
End Sub

**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.