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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
L

Legacy 98055

Guest
Initialize similiar to load
Layout is similiar to resize in VB.
tom
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
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.
 
L

Legacy 98055

Guest
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
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120

ADVERTISEMENT

...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).
 
L

Legacy 98055

Guest
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
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959

ADVERTISEMENT

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>
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
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.
 

dmckinney

Board Regular
Joined
Jul 10, 2002
Messages
120
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.
 

jbrumbau

New Member
Joined
Oct 13, 2011
Messages
1
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.
 

Forum statistics

Threads
1,144,210
Messages
5,723,047
Members
422,476
Latest member
beck85

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
Top