Possible to have a UserForm close by itself?

[mcomp72]

For my workbook, I want a 'splash screen' to display immediately after the workbook has been opened. The workbook takes a 10-12 seconds to run a bunch of code when it is first opened, so I want to inform the user that the workbook is loading, so they don't think something is wrong. I created a UserForm, and placed code to open it in the Workbook_Open sub inside the ThisWorkbook module.

Private Sub Workbook_Open()

LoadingUserForm.Show

End Sub

The UserForm appears, though not as immediately as I would like. (That's actually a smaller issue at the moment, but if anyone has any thoughts as to why it might be taking a few seconds for the UserForm to appear, even though it is the ONLY thing inside the Workbook_Open sub, I'm all ears.)

I would like the UserForm to automatically unload (disappear) once all of the code inside its Initialize sub is finished running. I tried putting 'Unload Me' at the end of the Initialize sub, but it gave me a Run-time error 91.

I don't want the user to be able to close the UserForm on their own, so I have made it to nothing happens if they click the X in the top right.

I know it's possible to do this, because I've seen another workbook that does it, but the code to that workbook is password protected, so I can't see how they do it. I have searched online, but haven't had any luck finding an answer.

 

[Rick Rothstein]

I would like the UserForm to automatically unload (disappear) once all of the code inside its Initialize sub is finished running. I tried putting 'Unload Me' at the end of the Initialize sub, but it gave me a Run-time error 91.

Assuming your UserForm is named "LoadingUserForm"), put this...

Unload LoadingUserForm

as the last executable line of code inside your Initialize event.

 

[mcomp72]

Assuming your UserForm is named "LoadingUserForm"), put this...

Unload LoadingUserForm

as the last executable line of code inside your Initialize event.

Just tried it. It results in Run-time error 91: Object variable or With block variable not set, just like when I tried 'Unload Me'. (Just to clarify: the UserForm never shows up on the screen.)

If I remove that line, the UserForm does show up on the screen, but unfortunately doesn't ever go away.

 

[wwbwb]

I do something similar. I use .hide to get rid of the userform. Always works for me.

ie. LoadingUserForm.Hide

 

[Rick Rothstein]

Just tried it. It results in Run-time error 91: Object variable or With block variable not set, just like when I tried 'Unload Me'. (Just to clarify: the UserForm never shows up on the screen.)

I'm confused. In Message #1 you said "The UserForm appears, though not as immediately as I would like" but now you are saying "Just to clarify: the UserForm never shows up on the screen". Please clarify.

If I remove that line, the UserForm does show up on the screen, but unfortunately doesn't ever go away.

Remove what line?

 

[mcomp72]

After a lot more experimenting, I discovered that on the line of code that shows the UserForm, if I set it to Modeless, the unload command at the end of Initialize will work. I threw in a Repaint command because I was having an issue of the window showing up but nothing showing up inside.

LoadingUserForm.Show vbModeless
LoadingUserForm.Repaint

I do have another issue; I alluded to it in my original post. When the workbook opens, the little circle (what used to be the hourglass) spins for a few seconds BEFORE the UserForm is displayed. I don't understand what could be causing this, because the very first lines of my Workbook_Open sub are the ones above. Any idea what could be happening for a few seconds in Excel prior to the Workbook_Open sub being executed? My workbook does have a LOT of code and about 85 sheets, but I wouldn't think anything else would be happening BEFORE the Workbook_Open sub was executed. But I'm still fairly new to Excel VBA, so please enlighten me!

 

[mcomp72]

I'm confused. In Message #1 you said "The UserForm appears, though not as immediately as I would like" but now you are saying "Just to clarify: the UserForm never shows up on the screen". Please clarify.

What I mean was, when I inserted the line of code you recommended, the UserForm would no longer appear. I received the Run-time 91 error without the UserForm ever appearing.

 

[Jaafar Tribak]

After a lot more experimenting, I discovered that on the line of code that shows the UserForm, if I set it to Modeless, the unload command at the end of Initialize will work. I threw in a Repaint command because I was having an issue of the window showing up but nothing showing up inside.

I wouldn't run code from the userform initialize event. I would use the UserForm_Activate event.
If you do that then I think you won't need to repaint the userform, it will be faster in appearing and the hourglass issue may also be resolved..

 

[mcomp72]

Actually, right now I have ALL the code inside the Workbook_Open event in the ThisWorkbook module.

Private Sub Workbook_Open()

LoadingUserForm.Show vbModeless
LoadingUserForm.Repaint

'a lot of code to start up the workbook

Unload LoadingUserForm

End Sub

There is currently no UserForm_Initialize event or UserForm_Activate event.

I was figuring this would be the fastest way for it all to happen. If I moved all the other code inside the Userform_Activate event, do you that will make the LoadingUserForm appear faster? I'm at a loss to understand why it isn't showing up immediately, since it is literally the first thing in the Workbook_Open event. What else could Excel be doing before showing it? Could the fact that my workbook has 85 sheets cause it to do any "thinking" prior to executing the Workbook_Open event?

 

[Jaafar Tribak]

Actually, right now I have ALL the code inside the Workbook_Open event in the ThisWorkbook module.

Private Sub Workbook_Open()

LoadingUserForm.Show vbModeless
LoadingUserForm.Repaint

'a lot of code to start up the workbook

Unload LoadingUserForm

End Sub

There is currently no UserForm_Initialize event or UserForm_Activate event.

I was figuring this would be the fastest way for it all to happen. If I moved all the other code inside the Userform_Activate event, do you that will make the LoadingUserForm appear faster? I'm at a loss to understand why it isn't showing up immediately, since it is literally the first thing in the Workbook_Open event. What else could Excel be doing before showing it? Could the fact that my workbook has 85 sheets cause it to do any "thinking" prior to executing the Workbook_Open event?

I am not sure but the userform initialize event runs code before the userform is displayed as opposed to the Activate event.

Have you given the activate event a try and see the difference in speed ?

EDIT:

Now that you say you have all the code in the workbook open event - has speed improved ?

Also, are you running any long loops in the code that starts up the workbook ? Is the workbook hidden or visible while the userform splash screen is on display ? and is your code selecting ranges , worksheets etc ... ?

All the above can slow down the code considerably.