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

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


.

I have amended your code and copied below. You can copy/paste into the
appropriate place.

1. There was a spelling error in "UserFrom_Initialize()" which I have
corrected. You would get no error message because the name is valid for
a subroutine.

2. There is no point here bothering with the scope of variable names
because your form will retain the combo selection value until it is
unloaded, when it is lost. You therefore need to save it somewhere
before you unload the form. I have used Cell A1 here.

'====================
' WORKSHEET CODE
'====================
Private Sub Worksheet_Change(ByVal RangeChanged As Range)
If RangeChanged.Column = 2 And Curr_User = "" Then
'Load frmRequestor ' ** do not really need
frmRequestor.Show
' Curr_User = frmRequestor.cbxRequestor.Value ' not needed **
ActiveSheet.Range("A1").Value = _
frmRequestor.cbxRequestor.Value '**
Unload frmRequestor
End If
End Sub
'-- END OF WORKSHEET CODE ----------------

'==============
' FORM CODE
'==============
Private Sub Done_Click()
frmRequestor.Hide
End Sub


Private Sub UserForm_Initialize() '** originally misspelled
cbxRequestor.AddItem "Choice 1"
cbxRequestor.AddItem "Choice 2"
cbxRequestor.AddItem "Choice 3"
cbxRequestor.AddItem "Choice 4"
cbxRequestor.AddItem "Choice 5"
cbxRequestor.ListIndex = 0 ' added ***
' MsgBox cbxRequestor '*** delete
End Sub
'-- END OF FORM CODE ----------------------


---
Message posted from http://www.ExcelForum.com/

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


.

Ok. Ok. I suppose I could put it into A1. But isn't there another way
to declare a variable who's scope is presistent enough to save this value?

"BrianB" wrote in message
...
I have amended your code and copied below. You can copy/paste into the
appropriate place.

1. There was a spelling error in "UserFrom_Initialize()" which I have
corrected. You would get no error message because the name is valid for
a subroutine.

2. There is no point here bothering with the scope of variable names
because your form will retain the combo selection value until it is
unloaded, when it is lost. You therefore need to save it somewhere
before you unload the form. I have used Cell A1 here.

'====================
' WORKSHEET CODE
'====================
Private Sub Worksheet_Change(ByVal RangeChanged As Range)
If RangeChanged.Column = 2 And Curr_User = "" Then
'Load frmRequestor ' ** do not really need
frmRequestor.Show
' Curr_User = frmRequestor.cbxRequestor.Value ' not needed **
ActiveSheet.Range("A1").Value = _
frmRequestor.cbxRequestor.Value '**
Unload frmRequestor
End If
End Sub
'-- END OF WORKSHEET CODE ----------------

'==============
' FORM CODE
'==============
Private Sub Done_Click()
frmRequestor.Hide
End Sub


Private Sub UserForm_Initialize() '** originally misspelled
cbxRequestor.AddItem "Choice 1"
cbxRequestor.AddItem "Choice 2"
cbxRequestor.AddItem "Choice 3"
cbxRequestor.AddItem "Choice 4"
cbxRequestor.AddItem "Choice 5"
cbxRequestor.ListIndex = 0 ' added ***
' MsgBox cbxRequestor '*** delete
End Sub
'-- END OF FORM CODE ----------------------


---
Message posted from http://www.ExcelForum.com/