VBA: Regarding Sub Auto_Open()

[jase71ds]

Hi,
I have an Autpen macro that cleans up data before allowing the user to begin. This only takes around 8 seconds, but I wanted to put a message asking the user to please wait a moment while we tidy things up.
What I found is that when I start my Autpen macro with MsgBox("Please wait a moment...") - the rest of the code will not run until the user hits "OK"
My preference would be that I could start by showing the message, but that Excel would continue to execute the remainder of the code and not wait until the user clears out the message.

Is this doable?

Thanks for any help.
Jase.

 

[RoryA]

It is doable - you need to create a userform with say a label or textbox containing the message you want, then show it modelessly at the start. For example:

With userformname
   .Show False
   .Repaint
End With

The repaint is just there to try and ensure it displays properly before processing the rest of the code.

 

[jase71ds]

I'm not familiar with userforms but I'll do some research.
Thanks for the info and code!

 

[jase71ds]

Rory,
I created a textbox (went with the default name of TextBox5) and tried the following within Autpen()

With TextBox5
.Show False
.Repaint
End With

The code broke at .Show False

Can you (or someone) give a bit more detailed guidance on this?
I'm assuming I create a message (as in a text box or shape) and that, upon open I call it first (letting the user know to be patient). Then I assume at the end of my Autpen() code, I close it.
Is this correct?
What am I doing wrong.
Thanks for any help.
Jase.

 

[RoryA]

You have to add a userform to your project, then add the textbox (or a Label) to the userform, and assign your text to that control.