Form Close help

[Emoncada]

I have 2 forms. How can I have it for if someone clicks the upper red X on the right of one of the forms it will show the other form.

Example

Closed Userform1 Then Show UserForm2

[Kenneth Hobs]

[VBA]Private Sub UserForm_Terminate()
AppActivate UserForm1.Caption
End Sub[/VBA]

[Emoncada]

That doesnt' seem to work. Are you sure it's .caption?

[Kenneth Hobs]

Well, be sure that you put it in the right Terminate event for the other userform.

[Aussiebear]

I would have thought that the OP is requesting when you close the first form by using the red X, the second form then shows.

How does .caption function?

[Bob Phillips]

[vba]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = 0 Then

UserForm2.Show
End If
End Sub
[/vba]

[Kenneth Hobs]

AppActivate will activate the window with that caption. The advantage over the Show method is that it does not reset the window position.

Of course you should probably check to see if the window is even open or code for an error. While no error would occur with the Show method, one want to only use it if the userform is open already as well.

One issue for me seems to be that both methods do not put the focus on the 0 index control, even when .Setfocus is used.

The QueryClose event is better than the Terminate event for the OP's needs.

[Aussiebear]

Thanks Kenneth

[Norie]

Kenneth

Why use AppActivate?

What if the form isn't open or loaded?

[Bob Phillips]

Kenneth

Why use AppActivate?

That seems sufficient enough a question to me, no need to qualify further. Totally unnecessary, totally redundant.

[Norie]

xld

That's a bit cryptic.

Is that post for me, the OP or Kenneth?

[Bob Phillips]

Yeah, after I posted it I figured it was. It was meant to affirm your comment, but also to suggest that asking why was sufficient, no need to justify that question. In other words, IMO AppActivate was totally the wrong suggestion.

[Kenneth Hobs]

If a solution works, I am not sure how that is bad. In post 7, I explained that something like the code below would be needed for a better routine. I guess it is up to the user to decide if they want to force a reposition using show just to re-activate the userform. For my tests, I assumed that ShowModal was False and moved focus to a cell before the close.

Even so, the focus to a control after the close to activate or show seems to be more of a concern to me.

If ShowModal=True for both userforms, then closing the 2nd and showing the 1st would be a non-issue.

Private Declare Function FindWindow Lib "user32" _
  Alias "FindWindowA" (ByVal lpClassName As String, _
  ByVal lpWindowName As String) As Long
  
Private Sub CommandButton1_Click()
  Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  If CloseMode = 0 Then
    If FindWindow("ThunderDFrame", UserForm1.Caption) Then AppActivate UserForm1.Caption
  End If
End Sub

[Norie]

Kenneth

Why bring Windows API or AppActivate in to this?

Surely what's being dealt with is Excel VBA userforms.

[Kenneth Hobs]

I guess post #7 was too complicated. After reading it again, I see that my grammar was not the best. I try to correct grammar and spelling mistakes when I find them and a forum has not locked the post by then.

So, to explain my reasoning a bit more as requested: I often consider other issues that someone else like the Original Poster might encounter. I guess they can always post back when they encounter that issue but I might be absent by then. Some people like this approach and some don't. In other words, I do my best to help and it is certainly not perfect. To recap post #7 and why I used FindWindow. I wanted to give the user an option to show or NOT show userform1. FindWindow is the first method that came to mind. After thinking on it a bit more, the userform's Visible property is the better method for that part.

Again, see post #7 for why I used AppActivate rather than Show.

All methods have advantages and disadvantages over other methods. You guys seem to like the Show method better than AppActivate. I leave it to each user to decide what they like best.

I hope that you found my reasoning helpful.

[Aussiebear]

This thread is heading in a non positive direction, therefore I'm closing it. Should the OP want an opportunity to post a responce to the advice given in the earlier posts, then please PM me.

With a passage of time, clearer minds will prevail.