Roedd <<davidm wedi ysgrifennu:

Thanks Bruce.

Rob, actually.

Using *Private Sub UserForm_Activate()* in place of the
Initialize event did the trick. Not for the first time, I am
confounded by the nuances between these two events. Does it seem
safer to use the Activate event in all cases?

No. these events have two entirely different puposes in the lifecycle of a
form, though this is not always clear if you show your forms in the
'default' manner.

The Initialise event (same as the Load event in VB forms) runs when the form
is first created. This can be via explicitly calling the Load method of the
form:

UserForm1.Load

creating an new object variable of the type of your form:

Dim frmMyForm as UserForm1
Set frmMyForm = New UserForm1

or by showing the form without first loading it (this causes the form to be
automatically loaded before being shown):

UserForm1.Show

The Activate event, in contrast, only fires when the form is shown. So for
the first two examples above it will not fire and for the third example it
will fire immediately after the Initialise event.

However, you should also note that the Activate event will fire again should
you hide (without unloading) and then show your form again. To see this in
action, create a form in an empty workbook, add a listbox and a button (keep
the default names) and then add the following code to the form:

Private Sub CommandButton1_Click()
Me.Hide
Me.Repaint
Application.Wait _
(Now + TimeValue("00:00:01"))
Me.Show
End Sub

Private Sub UserForm_Activate()
Me.ListBox1.AddItem "Activate"
End Sub

Private Sub UserForm_Initialize()
Me.ListBox1.AddItem "Initialize"
End Sub

Now run the form and click the button a few times and you'll see what I
mean.

Finally, add a new standard module to your project and paste in the
following:

Sub test()

Dim frmMyForm As UserForm1
Set frmMyForm = New UserForm1

frmMyForm.Show

End Sub

Place your cursor inside the procedure and hit F8. Note how the form events
occur at completely different points in the calling code.

HTH

Rob