Beginning forms
 

I am having trouble with some basic ideas of userforms.
I want to start a main procedure, fire off a form to collect
some data, then return to the main procedure with the
data gathered in the form. I would like to use VBA
variables to contain the data rather than spreadsheet cells.

----- In the main section of VBA -----
Option Explicit
Dim Result$
Sub Main()
frmOptionResult.Show
MsgBox Result$
End Sub
----- In the UserForm code -----
Private Sub UserForm.Terminate()
Result$ = IIF(OptionButton1.Value, "One", "Not One")
End Sub

Above is what I tried to do thinking that I could collect
Result$ in a global variable that could be referenced by
'MsgBox Result$' in Main(). Unfortunatelly, when the
form unloads, the Result$ collected goes out of scope.
I was closing the form by pressing "X", and that is what
fires off 'UserForm Terminate()'. Is there a more common
way to do this that preserves the data on the form?
Perhaps it needs to be hidden rather than terminated and
then unloaded later?

Beginning forms
 

You need to declare the Result variable using the Public
statement instead of the Dim statement. When you declare a
variable at module level (at the top of the module outside
of a procedure) using the Dim statement then it is
available to all procedures in that module only. Your user
form could not access the variable - i.e. Public Result$
should work.

On another point, attaching the dollar sign ($) at the end
of a variable when declaring it is a shorthand method of
declaring it as a string variable. It is more popular to
do it this way: "Dim Result as String". The dollar sign is
not considered part of the variable name. So when you
refer to it in your code it should be ommitted. It did not
generate an error in my test however. Examples of of type-
declaration characters follow:
$ String
@ Currency
# Double
% Integer
& Long
! Single

Regards,
Greg

-----Original Message-----
I am having trouble with some basic ideas of userforms.
I want to start a main procedure, fire off a form to
collect
some data, then return to the main procedure with the
data gathered in the form. I would like to use VBA
variables to contain the data rather than spreadsheet
cells.

----- In the main section of VBA -----
Option Explicit
Dim Result$
Sub Main()
frmOptionResult.Show
MsgBox Result$
End Sub
----- In the UserForm code -----
Private Sub UserForm.Terminate()
Result$ = IIF(OptionButton1.Value, "One", "Not One")
End Sub

Above is what I tried to do thinking that I could collect
Result$ in a global variable that could be referenced by
'MsgBox Result$' in Main(). Unfortunatelly, when the
form unloads, the Result$ collected goes out of scope.
I was closing the form by pressing "X", and that is what
fires off 'UserForm Terminate()'. Is there a more common
way to do this that preserves the data on the form?
Perhaps it needs to be hidden rather than terminated and
then unloaded later?


.

Thanks Greg! Beginning forms
 

Yipee! Thanks Greg!
-Tim

"Greg Wilson" wrote in message
...
You need to declare the Result variable using the Public
statement instead of the Dim statement. When you declare a
variable at module level (at the top of the module outside
of a procedure) using the Dim statement then it is
available to all procedures in that module only. Your user
form could not access the variable - i.e. Public Result$
should work.

On another point, attaching the dollar sign ($) at the end
of a variable when declaring it is a shorthand method of
declaring it as a string variable. It is more popular to
do it this way: "Dim Result as String". The dollar sign is
not considered part of the variable name. So when you
refer to it in your code it should be ommitted. It did not
generate an error in my test however. Examples of of type-
declaration characters follow:
$ String
@ Currency
# Double
% Integer
& Long
! Single

Regards,
Greg

-----Original Message-----
I am having trouble with some basic ideas of userforms.
I want to start a main procedure, fire off a form to
collect
some data, then return to the main procedure with the
data gathered in the form. I would like to use VBA
variables to contain the data rather than spreadsheet
cells.

----- In the main section of VBA -----
Option Explicit
Dim Result$
Sub Main()
frmOptionResult.Show
MsgBox Result$
End Sub
----- In the UserForm code -----
Private Sub UserForm.Terminate()
Result$ = IIF(OptionButton1.Value, "One", "Not One")
End Sub

Above is what I tried to do thinking that I could collect
Result$ in a global variable that could be referenced by
'MsgBox Result$' in Main(). Unfortunatelly, when the
form unloads, the Result$ collected goes out of scope.
I was closing the form by pressing "X", and that is what
fires off 'UserForm Terminate()'. Is there a more common
way to do this that preserves the data on the form?
Perhaps it needs to be hidden rather than terminated and
then unloaded later?


.