asked on

Userform_Activate event not triggered

Using VBA in Excel97, it is my understanding that the userform's Activate event occurs when the userform becomes the active window. I've performed simple tests to verify this but I've got an application where the Activate event only seems to occur the first time the userform is shown. Once shown and then hidden using the Hide method, the Activate event does not occur the next time the userform is shown.

I've seen something like this on Microsoft's support web page but that problem involved the Deactivate event and a second userform, neither of which apply to me.

Any ideas what may be causing my problem?

Noggy

Your simple tests showed that the Activate event worked OK? It should do.

I take it you are referring to this document with the 2 forms: http://support.microsoft.com/support/kb/articles/Q167/3/83.ASP

I also presume that in your troubled app, you're not moving the focus from your form when you hide it and later re-show it. If so, maybe the focus is not automatically being swapped to your Worksheet etc.?

Noggy

I've just tried it actually with a simple form and code with two .Show commands consectively. What I said is true: as you've hidden the form, the Activate event will not be triggered when you Show the form for the second time.

The only way it'll work between the 2 Shows is to unload the form after the first show:

I.e.:
Public Sub Test()
UserForm1.Show'Activate event works

UserForm1.Show'Activate event doesn't work
End Sub

Public Sub Test()
UserForm1.Show'Activate event works
ActiveSheet.Activate
UserForm1.Show'Activate event doesn't work
End Sub

Public Sub Test()
UserForm1.Show'Activate event works
Unload UserForm1
UserForm1.Show'Activate event works
End Sub

This doesn't help you much but is it possible for you to unload the form prior to the second show?

z5w

ASKER

I agree with what's been said but is that the way it's suppose to work? I don't want to Unload the userform because I want to show it as the user left it the previous time it was shown...if I Unload it, all the controls will lose their values.

ASKER CERTIFIED SOLUTION

Noggy

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ture

z5w,

Some additional information:

This really seems to be a bug and it has been fixed in Excel 2000. Here is my test:

Module1:
Sub test()
UserForm1.Show
MsgBox "UserForm1 is hidden"
UserForm1.Show
End Sub

UserForm1:
(A form with only a commandbutton)

Private Sub UserForm_Activate()
MsgBox "Activate!"
End Sub

Private Sub CommandButton1_Click()
Me.Hide
End Sub

When running the procedure 'test' in Excel 97 SR-2, this happens:
1. UserForm1 is displayed
2. The 'Activate!' message shows up
3. I click the command button
4. UserForm1 is hidden
5. The 'UserForm1 is hidden' message shows up
7. UserForm1 is displayed again
(no 'Activate!' message)
8. I click the command button
9. UserForm1 is hidden

When running the procedure 'test' in Excel 2000, this happens:
1. UserForm1 is displayed
2. The 'Activate!' message shows up
3. I click the command button
4. UserForm1 is hidden
5. The 'UserForm1 is hidden' message shows up
7. UserForm1 is displayed again
8. The 'Activate!' message shows up again
9. I click the command button
10. UserForm1 is hidden

Ture Magnusson
Karlstad, Sweden

Noggy

Hmmm, nice one, Ture. At least MS have implemented one fix for 2000. Maybe MS implemented my workaround :-) ?

z5w

ASKER

Thanks to both of you for your help.

Noggy

You're welcome, z5w, and thanks for the points.