Set Cell value form ListBox.

[eme]

I want to populate a cell with the user selection from a ListBox.
The sequence of events should be:
1. User positions to a cell that is the receive the data
2. User clicks a command button that executes the Userform/ListBox
3. The code populates and displays the ListBox
4. User selects an item from the listBox by clicking on the item
5. Without any more user actions, the code should end the ListBox/UserForm
and populate the previously selected cell with the ListBox value selected
Items 1 to 4 are working fine, I have trouble to have item 5 automatically
execute

Thanks for your help

 

[Nigel]

ActiveX object on sheet........assumes you only select one item.

Private Sub ListBox1_Change()
ActiveCell = Listbox1.Value
End Sub

 

[eme]

Thanks for your response Nigel,

Sorry I was not clear, what I am looking for is the automatic
Close/Cancel of the ListBox & UserForm after the selection is made.
Something like ListBox.Close & UserForm.Close.

I am migrating this VB code form Lotus.123 where it works fine.

 

[Nigel]

If the ListBox is on a UserForm then use the UserForm control change event
to populate the currently selected cell and unload the UserForm. Code is in
the UserForm

Private Sub ListBox1_Change()
ActiveCell = ListBox1.Value
Unload Me
End Sub

 

[eme]

If the ListBox is on a UserForm then use the UserForm control change event
to populate the currently selected cell and unload the UserForm. Code is in
the UserForm

Private Sub ListBox1_Change()
ActiveCell = ListBox1.Value
Unload Me
End Sub


--

Regards,
Nigel
(e-mail address removed)





Thanks for the help Nigel,

the bit that i was missing was the UNLOAD, all working now thanks