Message box to wait for calculation to complete

[Sheripres]

Hello:

I would like to create (hopefully) an Event Code that once data is put in cell B2, a message box would pop up to tell end user to "wait for calculation to complete" and once it is done, it will read, "calculation is completed".

This form I created takes about 10 to 15 seconds for it to go through 70,000 rows of data and I don't want the end user to interrupt that code.

Thank you,

Sheri

 

[Sheripres]

Michael,

ABSOLUTELY PERFECT! Thank you so very much! This will be used in many other reports. I really appreciate you taking the time to help me and to teach me about Userforms.

Sheri

 

[mick0005]

Wonderful. Were my instructions enough to guide you through how/where to put the VBA, or did you need to use the file I uploaded? I hope you were able to learn a bit about the VBA interface and how it works through this.

 

[Sheripres]

They were perfect! Usually, I create recorded macros and I know how to manipulate them from there. I have also created many Event Codes. I never used a Userform before, so I am constantly learning something new! I will broaden my knowledge to Userforms now that you have helped me.

I go on many forums and (no joke) your instructions were the best I have ever received. When I help people with Excel, I am also very thorough with the instructions. It is better to have more info than less and your instructions were easy to follow - even for a person who probably never went into VBA.

 

[mick0005]

Great feedback. Thank you.

One thing I did think about.... I don't know if you have named your worksheet as something other than "Sheet 1" or "Sheet 2", but if you have not, I would advise you to do so, and then modify the code in the workbook object to reflect that. The issue with my code is that it applies against the entire APPLICATION object... so it will turn calculation to manual for ANY open workbook that has a sheet called "Sheet 1" or "Sheet 2". That could cause some issues for people who don't realize their calculation was turned off for their other workbooks

If you name your sheets something unique, and your code reflects these sheet names, then you shouldn't run into a problem.

 

[Sheripres]

Yeppers; I did modify the Sheet 1 to reflect what I named my sheet tab. I am good to go! I know that I will be adding another form in this workbook and know that I can do the comma (,) to add that next unique sheet tab once I have the info for it (thanks for that options!).

If there was a "customer service" card for me to fill out, you would get "A+"!

 

[mick0005]

Thanks Happy to help.