1st post in a week! need a "please wait" UserFom or MsgBox

[Eddie G.]

this is the coolest...with the cloud and all....

[keith]

I have a sheet that is pulling data from a MYSQL dbase and it takes up to 15 seconds for excel to "refresh" the query! I have a commandbutton that the user clicks to refresh the data among other things. After the user clicks this button I also need a message to appear until the refresh is completed - saying "please wait"

All help is appreciated..

[Damon Ostrander]

Hi Keith,

This doesn't use either a userform or message box, but it works.

Sub PleaseWait()
With ActiveSheet.Shapes.AddShape(msoShapeCloudCallout, 200, 150, 150, 100)
.Name = "Wait a bit"
.TextFrame.Characters.Text = "Please Wait..."
.TextFrame.HorizontalAlignment = xlHAlignCenter
.TextFrame.VerticalAlignment = xlVAlignCenter
End With
Application.OnTime Now + 10 / 86400, "WaitOver"
End Sub

Sub WaitOver()
ActiveSheet.Shapes("Wait a bit").Delete
End Sub

This just schedules the wait message to appear for 10 seconds, then deletes it. Since you want it to disappear when the data gets refreshed, I suggest you eliminate the line of code that schedules the deletion (using the OnTime method), and simply put the code that deletes the message into the worksheet's Change or Calculate event, whichever is triggered by the data refresh.

[Mark O'Brien]

Excellent idea Damon. I couldn't think of a way to do that.

[keith]

Good idea. I had not thought of that approach exactly.

However, I should have said in my first post that my refresh commandbutton is located on a rather large menu style userform. This presents a problem because the userform is in front of the draw layer where the shape appears. Even if I can control the shape location, the shape is still in the background. Thus users are still able to try and click other buttons because the userform is on top.

Basically what happens now is the user clicks "refresh" and the refresh button hangs (depressed) until the refresh is complete. After the refresh the userform must be active so the user can click other commandbuttons on the userform..

I hope I explained that correctly..

rigth now I am messin with 1.Hide Userform - 2.Show the shape and then 3.Show Userform but I am not sure how that will work out or how it will look..

any suggestions?

[keith]

repost