Re: UserForm handling

From: Peter Hewett (nospam_at_xtra.co.nz)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 20:06:59 +1200

Hi Roderick O'Regan

This is the method I always use:

Public Sub ShowFormTheTechnicallyCorrectWay()
    Dim frmT As frmTest

    Set frmT = New frmTest
    Load frmT
    frmT.Show
    MsgBox "Form complete"
    Unload frmT
    Set frmT = Nothing
End Sub

It' a little more verbose than:
     Load frmTest
     FrmTest.Show
    Unload frmTest

but it's also a hell of a lot more reliable in the long run.

No, you should not do the Set after the hide command. The statement:
    Me.Hide

is executed within the UserForm (normally by the OK and cancel buttons). So to
dismiss a form using a Cancel button (a command button control called btnCancel)
you'd use the following code:

Private Sub btnCancel_Click()
    Me.Hide
End Sub

The code that calls the form should set its instance variable to Nothing (as
above). Even this step is optional as if you don't explicitly set the form
variable to nothing VBA will do it for you when the procedure terminates.

As you can see you still have to use Load/Sshow/Unload. Set just assigns a
reference to an object (in this case a form) to a variable.

HTH + Cheers - Peter
 

"Roderick O'Regan" <roregan@businesstraining.co.uk.NOSPAM>, said:

>When I'm working with UserForms I usually write the following code to show
>it:
>Load frmNewForm
>frmNewForm.Show
>
>Likewise to remove it:
>frmNewForm.Hide
>Unload frmNewForm
>
>I've been reading in my VBA handbook that I should be using:
>frmNewForm.Show
>Set frmNewForm.Nothing (presumably I should also do this after the .Hide
>command?).
>
>Does that mean that I should use the 'Set..." statement rather than the
>.Load or .Unload commands? What are the benefits?
>
>Regards
>
>Roderick O'Regan
>