Variable scope question w/combo-box
This demonstrates the problem. The Curr_User in Done_Click()
is not the same as the as the Public one below. To show, if I use
Option Explicit, Done_Click() recognizes no Curr_User variable.
If I define it in Done_Click() so Done_Click() can compile, then
Curr_User goes out of scope when I exit Done_Click().
"Kevin Beckham" wrote in message
...
add the following line
Private Sub Done_Click()
Curr_User = cbxRequestor.Value
frmRequestor.Hide
End Sub
and delete the assignment in the Worksheet_Change sub
Kevin Beckham
-----Original Message-----
Below, when a cell is changed in Column 2, I need to pop
up a
small form to collect the Curr_User field. The calling
code ...
Option Explicit
Public Curr_User As String
Private Sub Worksheet_Change(ByVal RangeChanged as Range)
If RangeChanged.Column = 2 and Curr_User = "" Then
Load frmRequestor
frmRequestor.Show
Curr_User = cbxRequestor
Unload frmRequestor
End If
End Sub
frmRequestor only contains a combo-box to collect
Curr_User
and a 'Done' command button. Now see the form code ...
Private Sub Done_Click()
frmRequestor.Hide
End Sub
Private Sub UserFrom_Initialize()
cbxRequestor.AddItem "Choice 1"
cbxRequestor.AddItem "Choice 2"
cbxRequestor.AddItem "Choice 3"
cbxRequestor.AddItem "Choice 4"
cbxRequestor.AddItem "Choice 5"
MsgBox cbxRequestor
End Sub
I need the result of the combo-box choice to be globally
accessable.
Curr_User comes out blank because I don't understand
scoping
of variables that well. How do I collect this result?
Thanks in advance,
-Tim
.
|