load event for userforms?

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
Hi,

Do excel forms have an event equivalent to the VB form_load event? i.e. an event triggered immediately following the .show

(also what's the layout event?)

Thanks in advance.

Dave McKinney.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Tom,

If I have code like ...

Code:
    Dim myform As frmModal
    Set myform = New frmModal
    myform.Caption = "acanthus"
    myform.VLdate = #1/1/2002#    
    myform.Show vbModal

... the initialise event is triggered on initialising the variable (set myfo...) The event I'm looking for is triggered after the myform.show

Also for the layout event...Isn't 'Resize' (rather than layout) like the vb resize event? (Except it seems to me that the user can't resize the forms - it has to be done with code.)

Thanks,

Dave.
 
Upvote 0
Try the Activate event.
Initialize in VB is basically for MDI's.
Resize is relative to Layout. There are several other controls which will fire this form event
 
Upvote 0
...again 'Activate' isn't the same as Load.
It's an event I never use cos it's so unpredictable when it may be triggered. (i.e. too often)

VB Initialise = Excel Initialise
VB Activate = Excel Activate
VB Load = Excel ?

Seems to me like Excel is missing this event.

These distinctions are important for me cos I'm passing values to and from modal forms. I can work around it, but it's just surprising I have to.

Has anyone anything to add to this....or to the mysterious layout event? (like an example).
 
Upvote 0
Hi DM
I don't understand why you would need a load event with VBA? When the form is initialized, it is loaded into memory. VBA does not directly support MDI's so there is no need for a separate event in between Initialize and Activate. If I'm lost help me find the way.
tom
 
Upvote 0
The only time that you should worry about the Activate event firing too many times is when you show another Userform and then close it, activating the original userform. To prevent this you can use this:<pre>If Me.Visible = False then
'Code to run to initialize variables only one time
End If</pre>
 
Upvote 0
Tom,

Thanks for sticking with me. I have a 'flair for flippancy' in my writing style sometimes so apologies if I've been a little brusque. Explanation refers to code sample below.

Code:
Dim myform As frmModal
    Set myform = New frmModal
    myform.Caption = "acanthus"
    myform.VLdate = #1/1/2002#    
    myform.Show vbModal
I guess the simplest way to describe it is that after the initialise event, I have access to the form properties (to SEND values). When I do frm.show vbmodal I no longer have access (as the code execution is passed to the form). When the form 'UNLOADS' (and code execution returns to the calling code), I can also read these properties (to RETURN values) as the form is still in memory until I do
Set myform=nothing.

The LOAD event is the code that 'does something with' the values I've sent in to the form properties. (These may be standard prps such as caption or Property Let / Get.)

Hope I'm being clear - it's not an easy one to explain. As I say I can code around it.
 
Upvote 0
Juan Pablo,

I can't get your code to work...
Code:
Option Explicit
Private mintTest As Integer

Private Sub CommandButton1_Click()
    Me.TextBox1.Text = mintTest
    
End Sub

Private Sub UserForm_Activate()
If Me.Visible = False Then
    'quietly change something.
    mintTest = 5
    
End If

End Sub

If this form has a button and a text box, the activate code never gets run. (XL 2000,W2000) Have I missed something.

But it seems also that you and Tom are correct in implying that excel activate is triggered much less than VB Activate.

Thanks,

Dave.
 
Upvote 0
Apparently you need to set the form's ShowModal property to false. Then set the Form_Load or Form_Activate function as public. Then right after you do UserForm1.Show, do UserForm1.Form_Activate.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top