I want to call a form from a procedure, selected the value of a
variable in the form, and pass the value back to the procedure. How can
I do this?

Here is what I've tried.

--------calling proc-----------

Public strVariable as String

Sub CallingProcedure()
Dim procVar1 as string
...

strVariable=""

frmUserForm14.Show vbModeless

cellLinkI = strVariable
...

End Sub
------------------------------
----code in frmUserForm14-----
(no declarations)

Public butOkay_Click()
Dim strFormVar as String

strFormVar=""
If strFormVar="X" Then
strVariable=strFormVar
Else
strVariable="Z"
End If

Unload Me

End Sub
------------------------------

When this code is run, the value of strVariable after the call is "".

Another way to do this might be to call the form with the variable
"ByRef" so that it is returned to the variable in the calling
procedure.

Maybe the problem is that the Form is not a Module.

Thank you.

John Wirt

How are you testing the value ? If it is in the module calling the form,
then as it is modeless the code will complete BEFORE the form action has
been completed.

I added the following I get the value correctly displayed....

' in the frmUserForm14 code
Private Sub UserForm_Terminate()
Call TestValue
End Sub

' in the main module
Sub TestValue()
MsgBox strVariable
End Sub




--
Cheers
Nigel



"John" wrote in message
oups.com...
I want to call a form from a procedure, selected the value of a
variable in the form, and pass the value back to the procedure. How can
I do this?

Here is what I've tried.

--------calling proc-----------

Public strVariable as String

Sub CallingProcedure()
Dim procVar1 as string
...

strVariable=""

frmUserForm14.Show vbModeless

cellLinkI = strVariable
...

End Sub
------------------------------
----code in frmUserForm14-----
(no declarations)

Public butOkay_Click()
Dim strFormVar as String

strFormVar=""
If strFormVar="X" Then
strVariable=strFormVar
Else
strVariable="Z"
End If

Unload Me

End Sub
------------------------------

When this code is run, the value of strVariable after the call is "".

Another way to do this might be to call the form with the variable
"ByRef" so that it is returned to the variable in the calling
procedure.

Maybe the problem is that the Form is not a Module.

Thank you.

John Wirt

OK, what I didn't figure out is that, when the form is called
"modelessly" from a procedure, the procedure will complete before the
code in the code is completed.

I can turn the programming around to call the form first to set the
variable and then call the procedure.

Will try.

Thank you.

John

John,

The problem is caused because you set the variable strFormVar to "" just
prior to testing it in butOkay_Click(). It will always be "", so you will
always set strVariable to "".

--

HTH

RP
(remove nothere from the email address if mailing direct)


"John" wrote in message
oups.com...
I want to call a form from a procedure, selected the value of a
variable in the form, and pass the value back to the procedure. How can
I do this?

Here is what I've tried.

--------calling proc-----------

Public strVariable as String

Sub CallingProcedure()
Dim procVar1 as string
...

strVariable=""

frmUserForm14.Show vbModeless

cellLinkI = strVariable
...

End Sub
------------------------------
----code in frmUserForm14-----
(no declarations)

Public butOkay_Click()
Dim strFormVar as String

strFormVar=""
If strFormVar="X" Then
strVariable=strFormVar
Else
strVariable="Z"
End If

Unload Me

End Sub
------------------------------

When this code is run, the value of strVariable after the call is "".

Another way to do this might be to call the form with the variable
"ByRef" so that it is returned to the variable in the calling
procedure.

Maybe the problem is that the Form is not a Module.

Thank you.

John Wirt

Bob,

My sample code would have made more sense if I had set strFormVar to
read the value of a text box on the form,

strFromVar=txtTextBox.Value

where txtTextBox is the name of the control. In the code example I
gave, unfortunately it doesn't matter what the value of strFormVar is.
I should have been more "precise."

Thank you.

John