Activate .. Select .. Initialize

[Tendresse]

What's the difference between:

Private Sub UserForm_Activate()
and
Private Sub UserForm_Initialize()

Also what's the difference between

Sheets("Sheet1").Select
and
Sheets("Sheet1").Activate

Many thanks, Tendresse

 

[Bob Phillips]

Activate is an event triggered when you show the form, Initialize when you
load it. If you show it before loading it (show does an implicit load if not
already loaded), it will Initialize then Activate. By this means you can
control what happens if the form is being shown for the first time or not.

With a sheet, Select and Activate is the same thing.

 

[JLGWhiz]

UserForm_Activate is an event that occurs as a result of the
UserForm#.Show command and occurs at the point that the
UserForm takes focus.

UserForm_Initialize is also an event that occurs as a result of
the UserForm#.Show command and occurs before the Activate
event.

Sheets("Sheet1").Select and Sheets("Sheet1").Activate are
essentially the same. However, If you use something like
Thisworkbook.Sheets.Select and then Sheets("Sheet1").Activate,
All of the sheets in the workbook will be selected, but only Sheet1
will be the active sheet. You can have only one active sheet at a
time but you can select any number of sheets. Same thing with cells
and ranges. Only one cell can be active, but many can be selected.

 

[Tendresse]

Thanks for the clarification, guys. Much appreciated.

Another question if you don't mind. With:

Userform1.Show
Set Userform1 = Nothing

What does 'Set Userform1 = Nothing' do exactly? I saw it in one of the
threads and i'm using it everytime I 'show' a form but i don't know exactly
what would happen if I don't put it there. Are you able to shed some more
light on this one, please?
Thanks once more .. you guys are gems.
Tendresse

 

[JLGWhiz]

When you set any object to Nothing, you remove any memory usage or reserve
for that object, including its properties. You would have to re-establish
the item to use it again in that instance of the procedure.

 

[Bob Phillips]

When you show a form, it creates an implicit instance of that class, so in
good programming practice you should release that object at the end.

As an example, consider a form with this code

Private Sub cmdQuit_Click()
Me.Hide
End Sub

Private Sub UserForm_Activate()
MsgBox "Activate"
End Sub

Private Sub UserForm_Initialize()
MsgBox "Initialize"
End Sub

and then you use this code to show it

UserForm1.Show
UserForm1.Show
Set UserForm1 = Nothing
UserForm1.Show


The first Show will MsgBox Initialize and the Activate, the second only the
Activate as it is already in memory, but then you clear out the form
instance, so the third show MsgBox the Initialize and Activate.

I use a variation along these lines

Dim myForm As UserForm1

Set myForm = New Userform1
With myForm

'do initialisation stuff
.Show

'do tidy-up stuff
End With

Set myForm = Nothing