Didn't get an answer last time. Perhaps my method of asking was wrong.

From Worksheet_Change I need to pop up a small form
to collect the Curr_User field and return the result to the caller.

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
' Can't access the value here because cbxRequestor.value
' goes away as soon as frmRequestor.Hide executes.
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 ...

Option Explicit
Private Sub Done_Click()
' Curr_User = cbxRequestor.Value 'Can't do this because
' as soon as
Done_Click()
' exits,
Curr_User goes
' out of scope.
' It was suggested that I do ActiveSheet.Range("A1").Value =
' frmRequestor.cbxRequestor.Value to conserve the value.
' Isn't there another way to declare a variable to save the value
' instead of using using a worksheet cell?
frmRequestor.Hide
End Sub

Thanks in advance,
-Tim

First, you should change how the worksheet_change event is defined:

Private Sub Worksheet_Change(ByVal Target As Range)

And change your RangeChanged references to Target.

And I don't think the value goes away when the userform is hidden. It will go
way after the unload, though.

frmrequestor.cbxrequestor.value
would retrieve it for you if the userform is still loaded.

Or put declare a variable in a general module as public, assign the value to it
and refer to that variable.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Curr_User = "" Then
Load frmrequestor
frmrequestor.Show
' Can't access the value here because cbxRequestor.value
' goes away as soon as frmRequestor.Hide executes.
MsgBox frmrequestor.cbxrequestor.Value
Unload frmrequestor
End If
End Sub

And I'd put this line in a general module:
Public Curr_User As String



Tim Coddington wrote:

Didn't get an answer last time. Perhaps my method of asking was wrong.

From Worksheet_Change I need to pop up a small form
to collect the Curr_User field and return the result to the caller.

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
' Can't access the value here because cbxRequestor.value
' goes away as soon as frmRequestor.Hide executes.
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 ...

Option Explicit
Private Sub Done_Click()
' Curr_User = cbxRequestor.Value 'Can't do this because
' as soon as
Done_Click()
' exits,
Curr_User goes
' out of scope.
' It was suggested that I do ActiveSheet.Range("A1").Value =
' frmRequestor.cbxRequestor.Value to conserve the value.
' Isn't there another way to declare a variable to save the value
' instead of using using a worksheet cell?
frmRequestor.Hide
End Sub

Thanks in advance,
-Tim

--

Dave Peterson

First, you should NOT change how the worksheet_change event is defined.
(oops)
But you should fix yours.

Dave Peterson wrote:

First, you should change how the worksheet_change event is defined:

Private Sub Worksheet_Change(ByVal Target As Range)

And change your RangeChanged references to Target.

And I don't think the value goes away when the userform is hidden. It will go
way after the unload, though.

frmrequestor.cbxrequestor.value
would retrieve it for you if the userform is still loaded.

Or put declare a variable in a general module as public, assign the value to it
and refer to that variable.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Curr_User = "" Then
Load frmrequestor
frmrequestor.Show
' Can't access the value here because cbxRequestor.value
' goes away as soon as frmRequestor.Hide executes.
MsgBox frmrequestor.cbxrequestor.Value
Unload frmrequestor
End If
End Sub

And I'd put this line in a general module:
Public Curr_User As String

Tim Coddington wrote:

Didn't get an answer last time. Perhaps my method of asking was wrong.

From Worksheet_Change I need to pop up a small form
to collect the Curr_User field and return the result to the caller.

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
' Can't access the value here because cbxRequestor.value
' goes away as soon as frmRequestor.Hide executes.
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 ...

Option Explicit
Private Sub Done_Click()
' Curr_User = cbxRequestor.Value 'Can't do this because
' as soon as
Done_Click()
' exits,
Curr_User goes
' out of scope.
' It was suggested that I do ActiveSheet.Range("A1").Value =
' frmRequestor.cbxRequestor.Value to conserve the value.
' Isn't there another way to declare a variable to save the value
' instead of using using a worksheet cell?
frmRequestor.Hide
End Sub

Thanks in advance,
-Tim

--

Dave Peterson


--

Dave Peterson