Statusbar / Screen display - out of sync

[dwooldridge]

OK, this is a bit weird. In Excel 2007. I want a light-weight way to show progress while I read a bunch of data from the database. The process is this: The user presses a button (ActiveX control) on the worksheet. We go to VBA code that calls a bunch of subs to read data:

private sub btnReadData_Click()

call suba
call subb
call subc
call subd

end sub

So I hit upon this idea. At the start of 'btnReadData_Click' change the color of the button. At the start of each 'suba', 'subb', etc. write a message to the status bar telling what we are doing. After the last 'subd' is execute change the button color back to its original color. So we have:

private sub btnReadData_Click()

btnReadData.backcolor = green ' I use some hex constant to set to green

call suba
call subb
call subc
call subd

btnReadData.backcolor = vbButtonFace ' Or whatever the constant is to set to std. button color

end sub

Here's the interesting thing. The first time I press the button it turns green, status messages popup, and then the button color goes back to standard color - great! On subsequent executions it 'appears' that the button turns green, status messages start being output, the button goes back to the original color, status messages continue to be output and eventually we are done.

Its like the button color change is asynchronous - it changes back too early! Anybody ever see this kind of behavior before? I don't believe 'screenupdating' is being turned off/on anywhere. I tried DoEvents but that didn't seem to have any impact. The two backcolor statements I show above are the only ones in the code.

Very odd.