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

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
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..
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top