QueryClose Function Help

[somail]

Hi,

I am trying to make it so when the current userform exits the userform unloads and a Userform named Menu is displayed. This is ment to catch both a user pressing the X button or the userform closing when the Complete button is pressed.

As of right now, Menu is displayed but the exited userform "LVL1" continues to be shown in the background.




Private Sub done()

'Function is called from when a command button is pressed ( Complete_Click() ).
'Cleans up the worksheets and unloads the current userform LVL1 and automatically calls queryclose due to userform exiting.

doneBool = True
Application.DisplayAlerts = False
Sheets("Rings").Delete
Application.DisplayAlerts = True

Unload LVL1

End Sub


Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)

'loads menu. Boolean checks to make sure Unload LVL1 is not called twice.
'Boolean set in function Done() and is declared globally as a global variable.

If doneBool = False Then
Load Menu
Application.DisplayAlerts = False
Sheets("Rings").Delete
Application.DisplayAlerts = True
Unload LVL1
Menu.Show
Else

Load Menu
Menu.Show
End If

End Sub




Thanks for any help you guys can give me. I have been trying to wrap my head around this and I just can not get it.

[Aflatoon]

As soon as you show the menu form, your code stops (assuming Menu is modal) so the form does not actually unload. Is this form loaded from the Menu form? If so, it should not be necessary to show the menu from this form, the Menu form should hide itself, show this form then show itself again.

[somail]

ahhh I see. I didn't know the show modifier causes the code to stop there. I guess that makes sense. I tried modifying my code and I am so close. I just keep getting a "Object Variable or With Block variable not set" error, when I exit back to menu.

You are correct in your assumption, lvl1 loads from menu and then upon close should go back to the menu. Very simple but for some reason is the biggest hurdle I have had in the whole project.

Here is what I have.

In menu:



Private Sub LVL1_Button_Click()

'clicking this should load LVL1 and unload menu.
'also makes sure the menu is not still visable after unload.

Level = True
resetclick = True
Menu.Hide
Unload Menu
Load LVL1

'According to Debugger this is where the error is when I exit back to the menu.
'This makes no sense as LVL1 should be unloaded at exit and this commandbutton requires a manual press.

End Sub

Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)

'generic control for the "x" button to make sure things save.

If Level = False Then ActiveWorkbook.Close True


End Sub


In Lvl1



Private Sub UserForm_Initialize()

'populates worksheet and displays the userform

'Various Code

lvl1.show

End sub

Private Sub Complete_Click()


'User clicks this when work on lvl1 is done. Should return user to menu.
'I use Queryclose to control unload and loading since it runs no matter what.

Worksheets("Data").Range("Level").Value = 2
Worksheets("Data").Cells(4, 2).Value = Now

Unload LVL1


End Sub

Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)

'I use this to cleanup the worksheet and return to menu should the user be done or if user presses the "X" button.

Application.DisplayAlerts = False
Sheets("Rings").Delete
Application.DisplayAlerts = True
Unload LVL1
Load Menu

End Sub




I'm so Close!!! What am I doing wrong?

[Aflatoon]

Private Sub LVL1_Button_Click()

'clicking this should load LVL1 and unload menu.
'also makes sure the menu is not still visable after unload.

Level = True
resetclick = True
Me.Hide
LVL1.Show
Me.Show
End Sub
and then
Private Sub UserForm_Initialize()

'populates worksheet and displays the userform

'Various Code


End Sub

Private Sub Complete_Click()
'User clicks this when work on lvl1 is done. Should return user to menu.
'I use Queryclose to control unload and loading since it runs no matter what.

Worksheets("Data").Range("Level").Value = 2
Worksheets("Data").Cells(4, 2).Value = Now

Unload Me

End Sub

Private Sub Userform_QueryClose(Cancel As Integer, CloseMode As Integer)

'I use this to cleanup the worksheet and return to menu should the user be done or if user presses the "X" button.

Application.DisplayAlerts = False
Sheets("Rings").Delete
Application.DisplayAlerts = True

End Sub

[somail]

hmm... I still get the same error on the following:

Me.Hide
LVL1.Show
Me.Show

I tried to replace this with:

Unload Me
Load LVL1

and it still gives an error at this part when I exit out of LVL1. That section of code is not even relevant to exiting LVL1 so I have no idea why it throws an error. Especially since it already executes with no error when I go from Menu -> LVL1.

Thanks for the continued help!!

[Aflatoon]

Can you post the workbook? Don't need any data, just the two forms.

[somail]

Sure, see attached.

So I am basically trying to do 2 things:

1) If user clicks out of LVL1 then LVL1 is cleaned up and Menu is shown
2) When user completes LVL1 then the LVL is cleaned up, data is written and Menu is loaded to reflect the changes.

Hoprefully the comments in the code give you a good idea of what I am doing.

thank you for looking at this.

[Aflatoon]

Like this? You shouldn't show the form from its Initialize event - the calling code should take care of that. You also don't need to unload the form in the QueryClose event since that is already happening (that's what triggered the event).

[somail]

It works!!!! Thank you!!

Can you explain to me this portion under the LVL1 button click:


Private Sub LVL1_Button_Click()
'button click sends user to level 1

Level = True
Me.Hide
LVL1.Show
Me.Show
End Sub

Why do you hide the menu, show LVL1, and then show Menu at the same time? I am not sure how I can show the menu and LVL1 at the same time. Obviously it works, but I do not understand the logic.

Also, is there a way to make the menu reload (reinitialize) when I quit back to it after pressing the complete button in the LVL1 userform? This way the menu shows the updated status.


Thank you for all your help. This was a royal pain in my side. :beerchug:

[somail]

Edit: One more question. :-) Why do you prefer the show/hide modifiers versus unload? To me it seems like unload would be cleaner, but it really just seems to mess things up and I noticed you rarely use it.

[Aflatoon]

When you show the LVL1 form the button click code stops until the form is unloaded or hidden (because it is a modal form) so the menu hides, the lvl1 form is loaded and then the menu is only shown again once lvl1 is closed.
Ideally you would use a property in the lvl1 form that indicates whether it was cancelled or data was committed, and the menu form would read that and then react accordingly. For that to work you actually need to ensure the lvl1 form is only hidden and the calling code takes care of unloading it when the property has been checked. It's late here so I will create a revised version tomorrow if I have time and comment it so hopefully it will be clear what is happening, as well as where, when and why. ;)

[somail]

Thank you so much!!! It would help me very much.

EDIT: No need to follow up. I reread what you said earlier and now I understand. I was not fully understanding that the code completely stops running when the other form is shown. Thank you very much for the help. Everything works fine now.

[Aflatoon]

Well, I ended up making a few changes this morning on my journey to work so I though I may as well post them. I added a couple of properties and revised some of your code to give you some options to consider if nothing else. ;)

If there's anything that isn't clear and that interests you, just let me know.