CODEWITHSUNDEEP
vbaexceluserform
KawaRu
KawaRu

Reputation: 74

Closing any open userform

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

Upvotes: 0

Views: 6367

Answers (2)

Vityata
Vityata

Reputation: 43565

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.

Upvotes: 0

Tom
Tom

Reputation: 9878

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

Upvotes: 2

Related Questions