Passing a userform combobox value back to the calling sub? 1

[knifey]

Hi,
I have a sub that opens a userform and displays a combobox containing the 12 month names. The user chooses the correct month name then this should be passed back to the calling sub as variable iPeriod.
My problem is, I cannot seem to pass the combobox value back to the calling sub. I'm a bit of a newbie and suspect there is an easy answer to this. Can anyone advise where I may be going wrong?
I've included some lines of code so you can get some idea of what I mean.
Any advice will be greatly appreciated,
Roy

Module:
Sub convWestMiddlesex()
'dims
frmChooseMonth.Show
iPeriod = frmChooseMonth.cmbChooseMonth.Text
etc.
etc.
end sub


userform:
Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub

Public Sub cmdOK_Click()
Dim i As Range
Dim iResult As String
Dim iPeriod As String
iResult = cmbChooseMonth.Value
If iResult > "" Then
With Worksheets("Lists").Range("L_April")
Set i = .Find(iResult, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
If Not i Is Nothing Then
iPeriod = i.Offset(0, 2).Value
End If
End With
Me.Hide
Unload Me
Else
Call frmChooseMonth_Initialize
End If
End Sub

Private Sub frmChooseMonth_Initialize()
cmbChooseMonth.Clear
End Sub

 

[fumei]

You need to read up on Scope. This is critical when using variables.

iPeriod = frmChooseMonth.cmbChooseMonth.Text

fails because iPeriod is not in Scope.

iPeriod is declared on the userform (in your cmdOK procedure). Therefore it ONLY has Scope in THAT procedure. cmdOK. Nowhere else.

When the userform is closed, iPeriod (and any other variable declared in the userform module) is gone. So....

Module:
Sub convWestMiddlesex()
'dims
frmChooseMonth.Show
' focus nows passes to the userform
' the following will NOT execute until focus
' is backed BACK - the userform is closed
' and if it IS closed, which it must
' frmChooseMonth.cmbChooseMonth.Text doesn't exist
' [b]AND[/b] iPeriod does not exist
' as it was declared on the userform
iPeriod = frmChooseMonth.cmbChooseMonth.Text
etc.
etc.
end sub

Solution?

Declared a Public variable in the standard module:

' Module:

Option Explicit
Public iPeriod As String

Sub convWestMiddlesex()
... yadda
frmChooseMonth.Show

Now you can set iPeriod in any procedure in the userform module. You still can not do:

frmChooseMonth.Show
iPeriod = frmChooseMonth.cmbChooseMonth.Text

because of the reason stated above. When focus is passed back to the calling Sub, there is no frmChooseMonth.cmbChooseMonth.Text. It does not exist anymore.

faq219-2884

Gerry
My paintings and sculpture

 

[combo]

You can directly pass the value to global variable. Alternatively, in the userform code hide the form instead of unloading (Me.Hide), unload it in the procedure code after reading the values:

UserForm1.Show
MsgBox UserForm1.TextBox1.Text
Unload UserForm1

combo

 

[knifey]

Thanks for the info fumei,
It works like a treat!
Cheers,
Roy

 

[Andrzejek]

knifey, you never stated where and how do you call the Sub convWestMiddlesex from?

If you call it from your UserForm, you may just pass the value of the combo box:

...
Call convWestMiddlesex(Me.cmbChooseMonth.Text)
...

As long as your Sub looks like:

Sub convWestMiddlesex([blue]iPeriod As String[/blue])
[green]'dims
'frmChooseMonth.Show
'iPeriod = frmChooseMonth.cmbChooseMonth.Text[/green]
etc.
etc.
end sub

Have fun.

---- Andy

 

[fumei]

Andy, he stated that it was called from a module, not the userform.

Module:
Sub convWestMiddlesex()
'dims
frmChooseMonth.Show
iPeriod = frmChooseMonth.cmbChooseMonth.Text
etc.
etc.
end sub


userform:
Option Explicit

Private Sub cmdCancel_Click()
Unload Me
End Sub

etc.

Besides, unless the userform was previous .Hide, a Sub called from a userform that calls the same userform:

Sub convWestMiddlesex()
'dims
[b]frmChooseMonth.Show[/b]

will return a run-time 400 error.

knifey, combo is correct, you could indeed Hide, get the value, then unload. So my comment:

When focus is passed back to the calling Sub, there is no frmChooseMonth.cmbChooseMonth.Text. It does not exist anymore.

Should be more clearly stated: if you Hide the userform - and focus is passed back to the Sub....then you can in fact use a local variable.

Option Explicit

Sub yadda()
Dim strYadda As String  [COLOR=red]' local variable[/color red]
[COLOR=red]' Show userform[/color red]
UserForm1.Show
[COLOR=red]' do something on userform and .Hide
' userform is still loaded so you can
' get values from it[/color red]
strYadda = UserForm1.TextBox1.Text

[COLOR=red]' unload userform[/color red]
Unload UserForm1

[COLOR=red]' you can use the values in the local variable[/color red]
MsgBox strYadda
End Sub

Which route you use is dependent on whether you need a local (procedural) variable, or something you will be using at a module level. If it is just being used in the one procedure, that what the heck, make it local. If not, then you need to use a global variable.

Again, it is a matter of Scope.

faq219-2884

Gerry
My paintings and sculpture