0

After a good search and some (over)thinking I came to the conclusion that I have no answer on what seems to be a simple question.

I have an excel document with many (20+) userforms in it. If you press a button (that is not in the userform, but just on the excel sheet) to start over again it should close any userform that's open at that moment.

I tried with unload me but of course I got an error when there wasn't any open userform. Then I tried to add on error resume next thinking it would skip the line if there was no userform and therefore not giving an error but just continue what I want it to do. (opening a new userform).

It did indeed not give me the error anymore but it doesn't close any open userform as well (when there is one open).

So here I am, hoping someone here can help me as I don't know what to do. I could list up all of the userforms I suppose but it should be possible to go faster and automatically I suppose?

Some more info: It is never possible to have more than one userform open at the same time. // The button I want to create closes all the userforms if there are any and leads the user back to the main menu.

Thanks in advance! KawaRu

Improve this question
1
  • Unload Me won't work because Me is only in-scope within the UserForm's code module. So a button on a worksheet cannot invoke Unload Me, because there is no Me to unload :D Commented Feb 1, 2018 at 20:19

2 Answers 2

Reset to default
2

Try calling the following when you want to unload all forms

Sub UnloadAllForms(Optional dummyVariable As Byte) 'Unloads all open user forms Dim i As Long For i = VBA.UserForms.Count - 1 To 0 Step -1 Unload VBA.UserForms(i) Next End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thank you this works! Does this only count the open userforms? Starting from 0? Is that why you do VBA.UserForms.Count - 1
This only counts the open userforms and yes exactly the first userform will be 0
0

This is hopefully the worst code that I have written in the last 5 years, but it will close any breathing form in Excel that you may have (and will kill any variables etc) :

Public Sub CloseAll() End End Sub

Use with caution!

From the MSDN End Statement:

  • The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code.

  • Code you have placed in the Unload, QueryUnload, and Terminate events offorms and class modules is not executed.

  • Objects created from class modules are destroyed, files opened using the Open statement are closed, and memory used by your program is freed.

  • Object references held by other programs are invalidated.

  • The End statement provides a way to force your program to halt. For normal termination of a Visual Basic program, you should unload all forms.

  • Your program closes as soon as there are no other programs holding references to objects created from your public class modules and no code executing.

Improve this answer

6 Comments

The problem with this is that it doesn't want to execute the following code as well. So that's not really a sollution, but anyway, didn't know this so thank you :)
@KawaRu - what do you mean by "doesn't want"?
I mean that it really stops at end and doesn't continue with other code.
@KawaRu - this is the idea of End. It stops & kills anything. It is rather useful sometime.
Like using a sledge hammer to crack a walnut. It works but there's no edible walnut.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.