Userform Listbox Enter event

[antaeusguy]

I've inserted a Listbox into a Userform which property "Row Source" is linked to a range of cells. To make things simple, the ListBox has 3 values: Apple, Kiwi & Banana, obtaining from cell A1, A2 & A3.


What I want to achieve is that when I click on a fruit (say Apple) to select it, and when I hit Enter, the value Apple will be transferred to a cell (say in Cell C1).

I tried to use the Enter event for the list box:

Private Sub ListBox1_Enter()
Range("C1").Value = ListBox1.Value
Unload Me
End Sub

Unfortunately it gives an error message like below:

run-time error '-2147417848 (800101108)':
Automation error
The object invoked has disconnected from its clients.

Does anyone knows what did I did wrong in my code? Appreciate your help very much!

 

[JamesW]

Try _Click() instead of _Enter()

 

[Norie]

The enter event is triggered when the listbox is 'entered', eg tabbed to.

You could try the KeyPress event which will be triggered when a key is pressed.

 

[antaeusguy]

Hmm... I read the help file for KeyPress event

A KeyPress event can occur when any of the following keys are pressed:

• Any printable keyboard character.
• CTRL combined with a character from the standard alphabet.
• CTRL combined with any special character.
• BACKSPACE.
• ESC.

A KeyPress event does not occur under the following conditions:

• Pressing TAB.
• Pressing ENTER.
• Pressing an arrow key.
• When a keystroke causes the focus to move from one control to another.

So it can't read Enter.

For the click event, I find it's too quick to select it and it immediately transfer the value to cell. Because in reality I've thousands of lines, I have to click on it in order to hightlight the row, and using the up/down keys to scroll through the list to make a decision.

What I wished to achieve is to click on the line, scroll up/down using up/down arrow keys and when I finally found the right entry, I hit Enter and the value will be transferred to the cell.

 

[Norie]

There are other events available and perhaps even methods that don't directly involve an event.

What I'm thinking of there is the default property.