[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1355
  • Last Modified:

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?
0
z5w
Asked:
z5w
  • 5
  • 2
1 Solution
 
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 new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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
 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now