I have a listbox on a userform. The list displayed in the listbox comes from
a procedure (using .addItem). What I want to do is when an item from the list
is selected, that item is put in the active cell of the worksheet and I have
that working (code below). The problem I am having is that after I have made
a selection, I cannot update the next cell with the same value, I must select
another item from the list and many times I want to use the same item over
and over again. I have a cmdCloseFrm button on the form to stop the input
process.


Private Sub lstPickIt_Click()
Dim rngActive As String, stgrNew As String
rngActive = ActiveWindow.ActiveCell.Address
ActiveCell.Value = lstPickIt.Value
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Private Sub lstPickIt_Click()
ActiveCell.Value = lstPickIt.Value
ActiveCell.Offset(1, 0).Value = lstPickIt.Value
End Sub

-or-

Private Sub lstPickIt_Click()
Dim rngActive As Excel.Range
Set rngActive = ActiveCell
'Expand the range to 4 cells
Set rngActive = rngActive.Resize(4, 1)
rngActive.Value = lstPickIt.Value
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Billy B"
wrote in message
I have a listbox on a userform. The list displayed in the listbox comes from
a procedure (using .addItem). What I want to do is when an item from the list
is selected, that item is put in the active cell of the worksheet and I have
that working (code below). The problem I am having is that after I have made
a selection, I cannot update the next cell with the same value, I must select
another item from the list and many times I want to use the same item over
and over again. I have a cmdCloseFrm button on the form to stop the input
process.


Private Sub lstPickIt_Click()
Dim rngActive As String, stgrNew As String
rngActive = ActiveWindow.ActiveCell.Address
ActiveCell.Value = lstPickIt.Value
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub

Thanks..I couldn't get either to work right but playing around some more I
found that if I put the code in the lstPickIt_Dblclick() event procedure it
works great.

"Jim Cone" wrote:

Private Sub lstPickIt_Click()
ActiveCell.Value = lstPickIt.Value
ActiveCell.Offset(1, 0).Value = lstPickIt.Value
End Sub

-or-

Private Sub lstPickIt_Click()
Dim rngActive As Excel.Range
Set rngActive = ActiveCell
'Expand the range to 4 cells
Set rngActive = rngActive.Resize(4, 1)
rngActive.Value = lstPickIt.Value
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"Billy B"
wrote in message
I have a listbox on a userform. The list displayed in the listbox comes from
a procedure (using .addItem). What I want to do is when an item from the list
is selected, that item is put in the active cell of the worksheet and I have
that working (code below). The problem I am having is that after I have made
a selection, I cannot update the next cell with the same value, I must select
another item from the list and many times I want to use the same item over
and over again. I have a cmdCloseFrm button on the form to stop the input
process.


Private Sub lstPickIt_Click()
Dim rngActive As String, stgrNew As String
rngActive = ActiveWindow.ActiveCell.Address
ActiveCell.Value = lstPickIt.Value
ActiveCell.Offset(1, 0).Range("A1").Select

End Sub