CODEWITHSUNDEEP
excelvba
Origamer7
Origamer7

Reputation: 345

How can I close the UserForms properly in VBA in Excel, so that I can reopen them if necessary?

I created a "Workbook" in Microsoft Excel and I inserted three UserForms with the following

names:

  1. MainForm
  2. FirstForm
  3. SecondForm

I wrote code in the "Workbook_Open" event, so that my "MainForm" shows, when I open my

workbook. The code I wrote is the following:

Private Sub Workbook_Open()
   MainForm.Show
End Sub

My "MainForm" contains two buttons: 1. OpenFirstFormCommandButton 2. OpenSecondFormCommandButton

Each of the buttons has a click event, in which the "MainForm" hides and the corresponding

UserForm shows.

The click event of the "OpenFirstFormCommandButton" CommandButton which is on the "MainForm"

is the following:

Private Sub OpenFirstFormCommandButton_Click()
   MainForm.Hide
   FirstForm.Show
End Sub

When the "OpenFirstFormCommandButton_Click" event is triggered, the "MainForm" hides and the

"FirstForm" shows.

The click event of the "OpenSecondFormCommandButton" CommandButton which is on the

"MainForm" is the following:

Private Sub OpenSecondFormCommandButton_Click()
   MainForm.Hide
   SecondForm.Show
End Sub

When the "OpenSecondFormCommandButton_Click" event is triggered, the "MainForm" hides and

the "SecondForm" shows.

So far so good. That was all the code that is written for "MainForm".

Let's see the code which is written for the "FirstForm" and for the "SecondForm" now.

"FirstForm" contains code, only for the "UserForm_QueryClose" event, which is triggered in

case we try to close the form from the "X" button which appears at the upper right corner of

"FirstForm" form. The code is the following:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   FirstForm.Hide
   MainForm.Show
End Sub

The target of "UserForm_QueryClose" event, is to hide the "FirstForm" and Show the

"MainForm".

"SecondForm" contains the exact same logic as "FirstForm". Consequntly the only code for

"SecondForm" is in the "UserForm_QueryClose" event and is the following:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   SecondForm.Hide
   MainForm.Show
End Sub

That is all the code we have.

It is obvious, that I am trying to have a Main Form from which I can open other forms and in case I close these other forms, I want to return to my Main Form. That is actually what the code should do, but unfortunately, the code behaves a little funny.

When I open my Workbook the "MainForm" opens as it should do, because I have written such code in "Workbook_Open" event. When I click on "OpenFirstFormCommandButton" CommandButton, "FirstForm" shows while "MainForm" hides, which is also an absolutely normal behavior. Then I click on the "X" Button on the upper right corner of "FirstForm" and as a result the "FirstForm" hides and the "MainForm" shows. No funny behavior till now. The situation differs in the second time I click the "OpenFirstFormCommandButton" CommandButton which is on the "MainForm". The "FirstForm" shows, the "MainForm" hides and that is normal. When I try to click on the "X" button of the "FirstForm" (in the second time) the "FirstForm" does not hide and the "MainForm" does not show, although there is code in the "UserForm_QueryClose" event, which does exactly that.

The first question is, how can I overcome this problem? The second question is, why is the code deactivated? Why does not the code run?

In case you have faced this problem or you have any ideas how to overcome this situation, please send me a message.

Thank you in advance.

Upvotes: 2

Views: 1208

Answers (1)

GSerg
GSerg

Reputation: 78134

You're confusing the window manager.

By default .Show() shows a form modally (that is, there must be another form relative to which that form would be modal). In your circular setup this does not work well.

Replace all .Show in all forms with .Show vbModeless.

By the way you are not "closing forms properly." They remain loaded in memory, just not visible.

Upvotes: 3

Related Questions