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?
z5wAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NoggyCommented:
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.?
0
NoggyCommented:
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?
0
z5wAuthor Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

NoggyCommented:
Errrm, I wouldn't have thought so but I have found a workaround:

In your UserForm1 (where you want to keep the data), in the Click Event for the CommandButton where you hide the form, refer to a second UserForm (UserForm2) that you create that is blank. I.e.:

Private Sub CommandButton1_Click()
    Me.Hide
    UserForm2.Show
End Sub

In your Activate Event for UserForm2, put this:

Private Sub UserForm_Activate()
    Me.Hide
End Sub

When you press the button on UserForm1, your UserForm will be hidden and UserForm2 will be displayed and hidden instantaneously (i.e. you won't see it). Then, the Activate Event for UserForm1 will be run every time you hide and show the form. I.e. the following code will then work as you want:

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

Is this OK?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tureCommented:
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
0
NoggyCommented:
Hmmm, nice one, Ture. At least MS have implemented one fix for 2000. Maybe MS implemented my workaround :-) ?
0
z5wAuthor Commented:
Thanks to both of you for your help.
0
NoggyCommented:
You're welcome, z5w, and thanks for the points.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.