Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Add Beginning Text | Excel Discussion (Misc queries) | |||
How do i add " to the beginning of all cells | Excel Discussion (Misc queries) | |||
add "0" to the beginning of each value | Excel Worksheet Functions | |||
Zero at the beginning of a number | Excel Discussion (Misc queries) | |||
Calling Forms from Forms - Exit problems | Excel Programming |