Close a UserForm Via Code Only

[zoom38]

Hello,

I'm new to User Forms and am working on a simple UserForm that I would like to be able to close via code and not by command button or the title bar "X". Eventually I plan on hiding the UF title bar and not have any buttons on it. I've scoured the net and tried many ways with no success except in the Activate Event such as:

Private Sub UserForm_Activate()
   Application.Wait Now + TimeValue("00:00:02")
   Unload Me
End Sub

However, I do not want to use the activate event or a click event (button) to close it, is there another way?

Thx
Gary

[Aflatoon]

What would be the trigger for closing it then?

[zoom38]

What would be the trigger for closing it then?

I would like code to close it from an outside sub whether from a standard module or from the user form code. In a standard module I’ve tried the following:

UserForm1.unload
UserForm1 unload
unload UserForm1
unload.UserForm1

Ive also tried the above from within the user form code as well as using the Me designation with no success.

in all cases it either throws an error or it opens up a duplicate user form instead of closing it.

I’ve also assigned the escape key to close it which works, but only if the user form is activated/in focus. I’m opening up the user form vbModeless.

I’m beginning to think that it’s just not possible.

[jdelano]

What I did here was, create a module with a public variable of type UserForm1.
A button on sheet1 creates a new instance of it and then shows the form.
A second button on this sheet closes the form using unload.

Module1

Public userFormInstance As UserForm1

Sheet1

Private Sub btnCloseUserForm_Click()
    
    Unload userFormInstance
End Sub


Private Sub btnDisplayUserForm_Click()


    Set userFormInstance = New UserForm1
    userFormInstance.Show
    
End Sub

EDIT: You can of course use any code to do this, I just placed it in buttons for testing.

[zoom38]

What I did here was, create a module with a public variable of type UserForm1.
A button on sheet1 creates a new instance of it and then shows the form.
A second button on this sheet closes the form using unload.

Module1

Public userFormInstance As UserForm1

Sheet1

Private Sub btnCloseUserForm_Click()    
    Unload userFormInstance
End Sub


Private Sub btnDisplayUserForm_Click()


    Set userFormInstance = New UserForm1
    userFormInstance.Show
    
End Sub

EDIT: You can of course use any code to do this, I just placed it in buttons for testing.

Well I'll be dipped in horse hockey, I didn't think I was going to find a solution.
@jDelano, your code works flawlessly without buttons which was my intent.

Thank You

[jdelano]

You're welcome, happy to lend a hand.

[Aussiebear]

From horse hockey to nearly presentable to the mother in law...... great work jdelano.

[jdelano]

Bahahaha

[Paul_Hossler]

That seems unnecessarily complicated (if I'm understanding correctly)

Simple example of what I was thinking

3 subs, one loads and shows the UF, one interacts with it, and the last removes it

Option Explicit
Dim N As Long

Sub One()
    Load UserForm1
    UserForm1.Show vbModeless
End Sub

Sub Two()
    N = N + 1
    UserForm1.Label1.Caption = N
End Sub

Sub Three()
    UserForm1.Hide
    Unload UserForm1
End Sub

[Aflatoon]

That's basically the same principle except you are using the default instance of the form (which is effectively an implicit public variable), which is not really best practice, IMO.

[Paul_Hossler]

That's basically the same principle except you are using the default instance of the form (which is effectively an implicit public variable), which is not really best practice, IMO.

How so?

[zoom38]

That seems unnecessarily complicated (if I'm understanding correctly)

Simple example of what I was thinking

3 subs, one loads and shows the UF, one interacts with it, and the last removes it

Option Explicit
Dim N As Long

Sub One()
    Load UserForm1
    UserForm1.Show vbModeless
End Sub

Sub Two()
    N = N + 1
    UserForm1.Label1.Caption = N
End Sub

Sub Three()
    UserForm1.Hide
    Unload UserForm1
End Sub

Paul, I also agree your code is much simpler and easier to understand. I don't know where I went wrong with everything I tried. I couldn't get something very similar to work. Yours works great.

Thank you
Gary

[Aflatoon]

How so?

I'm assuming that relates to my "best practice" comment? If so, then Mathieu says it all here: https://rubberduckvba.blog/2017/10/25/userform1-show/

Apart from anything else, much like using Dim ... As New ... , you can never test if the object was unloaded/destroyed somewhere by mistake because any reference to it immediately creates a new instance of the object. I've lost count of the number of posts I've seen where people don't understand why reading a userform's control value returns blank when they could see it being input. It usually turns out that the form was unloaded by mistake somewhere but of course using formname.controlname simply reloads the form with its default setup, rather than producing an error, and makes debugging needlessly difficult. To me, it's a bit like not using Option Explicit and then spending hours chasing down bugs due to typos.

As always, that's not to say I wouldn't do it in quick and dirty one-off code, but I wouldn't use it in production that I expect to see serious use.