What happens during Userform activation?

[TonyJollans]

Hi,

I wonder if someone would be kind enough to explain to me the exact process which occurs during userform activation.

I have a userform which includes a progress meter and I want it displayed while I run some code. It all works, I have just been trying to understand why.

In my form's activate event code I hide some controls and I call my time-consuming routine. The form displays with the controls hidden but before the rest of my code runs. In other words some of the code in the event routine seems to run before the screen display and some after. What are the rules?

I have tried all sorts of things to investigate this but it seems that any interaction causes extra screen refreshes which interfere with my tests.

For what it's worth, my best guess is that the first code statement which does anything other than reference the userform object directly, triggers the display. But this seems a trifle obscure even for Microsoft.

If it's relevant, I'm using Excel 97. Or it's using me, I'm not sure which.

Thanks,
TonyHi,

I wonder if someone would be kind enough to explain to me the exact process which occurs during userform activation.

I have a userform which includes a progress meter and I want it displayed while I run some code. It all works, I have just been trying to understand why.

In my form's activate event code I hide some controls and I call my time-consuming routine. The form displays with the controls hidden but before the rest of my code runs. In other words some of the code in the event routine seems to run before the screen display and some after. What are the rules?

I have tried all sorts of things to investigate this but it seems that any interaction causes extra screen refreshes which interfere with my tests.

For what it's worth, my best guess is that the first code statement which does anything other than reference the userform object directly, triggers the display. But this seems a trifle obscure even for Microsoft.

If it's relevant, I'm using Excel 97. Or it's using me, I'm not sure which.

Thanks,
Tony

 

[RobBroekhuis]

I'm not sure I understand the contradiction. Which code runs before the screen display? How do you know this is the case (rather than the code running so fast, it completes in a "flash" right after the screen display)?

Rob

 

[TonyJollans]

Hi Rob,

Not only did I completely mess up the post but I also failed to be clear. Oh dear!

On my form I have, say, three controls, Label1, CommandButton1 and CommandButton2. My code is

Private Sub UserForm_Activate()

    CommandButton1.Visible = False
    CommandButton2.Visible = False
    
    MyRoutine

End Sub

'MyRoutine' (in a module) performs some checks on the worksheet and processes some data taking perhaps 20 seconds. During those 20 seconds the form is displayed with the commandbuttons hidden.

Yes, it is possible that some things happen too quickly for me to see but with ScreenUpdating set False there really shouldn't be anything changing at all, should there?

Thanks,
Tony

 

[RobBroekhuis]

Screenupdating applies to the worksheets, not to the userform (I'm pretty sure). My guess is that if you put a pause before the commandbutton.visible assignments, you'd first see the buttons, then they'd disappear, then there would be the 20 second wait. Unless of course the .visible property was already set to false before the activate event was called.
Rob

 

[TonyJollans]

Rob,

Before replying I went back and did some more testing and didn’t get exactly the results I thought I had got before, but I think I now understand. It seems that system interactions (MsgBox displays, Waits, Code Breaks, I’m not sure what else) act like mini DoEvents and artificially force screen refreshes. For some long-forgotten reason I has coded a one second wait in my routine which is what had forced my display and confused me. Coding tight loops to force delays allowed me to see what was going on.

When nothing forces the display what appears to happen is that at the start of the Activate Event, the form outline (Title bar and border) are displayed (I don’t know why) and at the end of the routine the rest of the form is filled in, so modifications to the form happen before the complete form is displayed.

Incidentally, ScreenUpdating does apply to forms – physical hiding of forms takes place after logical hiding – try something like the following code to see the delay.

Sub test()
  Dim a, b
  UserForm1.Show
  Application.ScreenUpdating = False
  UserForm1.Hide
  For a = 1 To 10000000
    b = a ^ 2
  Next
  Application.ScreenUpdating = True
End Sub

I think activating a form must force an override because the form can’t take the focus unless it is displayed.

Thanks for taking the time to reply and sorry for having troubled you with something I should have been able to work out myself.

Thanks,
Tony

 

[RobBroekhuis]

Hey, it's good to work on issues a little more involved then "how do I find the last cell in a column" once in a while
Rob