Excel 97: Pass variable from userform to Sub ? 
Author Message
 Excel 97: Pass variable from userform to Sub ?

Hi,

I have some VBA code which loads a userform (custom dialog box).
When the user presses commandbutton2, I want it to unload the userform,
(no problem there) and then kick out of the calling sub.

I can't seem to pass a variable back to the calling Sub to check if the
button was pressed.

Any and all ideas are appreciated.
Thanks,
Richard



Fri, 14 Sep 2001 03:00:00 GMT  
 Excel 97: Pass variable from userform to Sub ?
The trick is to unload the form after retrieving the variables set in the
form (unloading the form makes all values related to the form dissappear).
You can hide the form instead after Cancel or OK is pressed.  I usually do
the following:

IN THE USER FORM "TheForm":
Private cancelPressed As Boolean

Private Sub UserForm_Initialize()
    cancelPressed = False
    'Initialize settings of form here
End Sub

Public Property Get Cancel() As Boolean
    Cancel = cancelPressed
End Property

Private Sub CancelButton_Click()
    cancelPressed = True
    Me.Hide
End Sub

Private Sub OkButton_Click()
    'store data from form in private variables here
     Me.Hide
End Sub

IN THE CALLING SUB:
Dim myForm As TheForm
Set myForm = New TheForm 'create instance of form
myForm.show 'show the form
'Retrive stuff when cancel button is not pressed
If  Not myForm.Cancel Then
        'retrieve other stuff from form here via Get Property sub or
function in form
End If
Set myMethodsForm = Nothing 'Unload the form

Hope this helps.
-Rene

Quote:

> Hi,

> I have some VBA code which loads a userform (custom dialog box).
> When the user presses commandbutton2, I want it to unload the userform,
> (no problem there) and then kick out of the calling sub.

> I can't seem to pass a variable back to the calling Sub to check if the
> button was pressed.

> Any and all ideas are appreciated.
> Thanks,
> Richard



Fri, 14 Sep 2001 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. Passing Userforms to subs (Word 2000 and 2002, I did not try Word 97)

2. Passing a Variable from one Userform to another Userform

3. Passing Userforms to Subs

4. Pass variable to userform

5. Passing a variable through a UserForm

6. Passing variables between userforms and/or normal procedures

7. Passing variables or arrays to userforms

8. Passing variables or arrays to userform objects

9. Passing UserForm Variable To Class Module

10. Passing Variables between UserForm/Module


 
Powered by phpBB® Forum Software © phpBB Group