Make Excel visible when closing userform

Question

I have a button in an Excel worksheet that opens a userform when clicked. It also hides the worksheet, making it so you can only see the userform. Here's what I have to make that happen. This works fine.

Sub Button2_Click() Application.Visible = False frmDataColl.Show End Sub

Then I have this code that isn't working the way I want it to. Upon clicking the X to close the userform, I want the userform to close and also make Excel visible again. But this only closes the userform. It doesn't bring the workbook back into view.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Application.Visible = True End If End Sub

If CloseMode = vbFormControlMenu Then Debug.Print "Flag01" Application.Visible = True Add a debug print statement to make sure you are executing the statement inside the if condition — Abhinav Rawat
– Abhinav Rawat, Commented Sep 5, 2017 at 14:31
Adding that statement doesn't seem to be doing anything for me. — Robby
– Robby, Commented Sep 5, 2017 at 14:40
Your code works perfectly fine here. The only problem I'm seeing is that you're working off the form's default instance instead of treating it as an object, and that can (and usually does) lead to bugs. Change Button2_Click to do With New frmDataCol1 and .Show instead, and change your QueryClose implementation to Hide the form instance given a vbFormControlMenu close mode; that way you'll take control of the object's lifetime ("X" button destroys the object by default). Also, follow jkpieterse's advice. — Mathieu Guindon
– Mathieu Guindon, Commented Sep 5, 2017 at 15:11

jkpieterse · Accepted Answer · 2017-09-05 15:03:54Z

2

Get rid of the Queryclose event and change your code to:

Sub Button2_Click() Application.Visible = False frmDataColl.Show Application.Visible = True End Sub

answered Sep 5, 2017 at 15:03

jkpieterse

3,0591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mathieu Guindon Over a year ago

This. A form has no business controlling application visibility, especially if it didn't set it to False in the first place. I'd go one step further and use an instance of the form rather than do what all beginners do and work with a stateful default instance. New it up, it's an object! ...and do handle QueryClose - to prevent the form instance from self-destructing!

Robby Over a year ago

This works, but in addition to bringing back the workbook, it's creating another empty instance of Excel. Like there's a new Excel window, but a workbook isn't open in it.

jkpieterse Over a year ago

Mat: I was trying to make things simpler. I agree treating the form as an object and instantiating it properly is the best way to do things.

Mathieu Guindon · Accepted Answer · 2017-09-05 15:26:22Z

The form has no business knowing about Application.Visible state. As this answer shows, Button2 set it to False, it's Button2's job to set it back to True.

Your code works perfectly fine here - the problem is likely in code you haven't posted. Keep procedures' responsibilities well defined and you won't have these issues.

Another common trap you've fallen into (as did the other answer), is to work with a stateful default instance. Forms are objects - treat them as such!

Application.Visible = False With New frmDataCol1 .Show ' here you still have access to the form's state! End With Application.Visible = True

Now, if you don't handle QueryClose, that red "X" button effectively destroys the object and, normally, you absolutely don't want that.

What you want, is to control the object's lifetime yourself - that's what you have that With block for!

So you do handle QueryClose, but only to say "Hide the form instance, don't destroy it!" - like this:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) If CloseMode = vbFormControlMenu Then Cancel = True Me.Hide End If End Sub

That way when your dialog gets closed, the calling code can still access the public state and act accordingly. If you worked off the default instance, letting QueryClose destroy the instance causes the state to be lost. And since it's a default instance, a brand new one gets created as soon as you query that state, making the bug extremely difficult to find... if you don't know how objects with a default instance work.

Collectives™ on Stack Overflow

Make Excel visible when closing userform

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related