ComboBox Activation on WorkSheet

[gtvkeith]

Hi everyone,

I have just 'graduated' to Excel 2010 and boy, has it made life 'Interesting'!

I need help with an 'ActiveX' ComboBox located on a worksheet.

I am using it to activate AutoFilters on the worksheet, using a list of over a thousand names as reference.

My problem is that unlike a ComboBox on a UserForm the ComboBox Activates as soon as a unique 'value' is entered rather than waiting for the user to hit the enter key. Also if the user pauses too long while typing the name it will begin re-entering the value.

Does anyone know how to force the ComboBox to wait for the user to hit enter? I still want it to 'match' the values actively against the reference list though.

I have already changed its code to _Click() rather than _Change().

Thanks,

Keith.

 

[gtvkeith]

I am still having the problem stated above but have now determined that as soon as I add code to the ComboBox this issue presents itself.

All I did was put in a messagebox to tell me when the control triggers. But I still dont know why it works differently just because there is code behind it.

 

[Marcelo Branco]

Hi,

Maybe you are using the wrong control. A ComboBox is normally used to a user select a value from a pre-defined list (yes, is possible to Enter a new value in a ComboBox but i would not recommend this kind of use)

Have you thought about a TextBox and a CommandButton that would be pushed after the user had completed the entry in the text-box?

M.

 

[Jaafar Tribak]

Maybe something along these lines :

Private Sub ComboBox1_KeyDown _
(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

    If KeyCode = vbKeyReturn Then
        'do your stuff here.
    End If

End Sub

 

[gtvkeith]

Hi Marcelo, thanks for the reply.

I have a pre-defined list that is populating the ComboBox, currently 2,500 names.

My problem (I am used to working with ComboBoxes in Forms) is that with the ComboBox on the worksheet, as soon as I add code to the ComboBox, it's behaviour changes.

As soon as a unique entry from the list is typed (but not entered) the ComboBox activates, if the user is not aware this has happened and continue to type, they start entering data in whatever cell on the sheet has just become active.

Also if they pause while typing in the name (trying to remember how it is spelt) when they keep typing, the ComboBox treats this 'new' text as a new entry and starts filling the ComboBox again overwriting what was previously being typed.

What I am trying to do is get the ComboBox to behave just as it would in a UserForm.

I hope this clarifies my issue.

 

[Marcelo Branco]

Ok, now i understood. I think Jaafar code works (nice!)

M.

 

[gtvkeith]

Hi Jaafar,

Your code lets me keep typing without the ComboBox activating, but now I cannot get the ComboBox to activate at all.

 

[Marcelo Branco]

Hi Jaafar,

Your code lets me keep typing without the ComboBox activating, but now I cannot get the ComboBox to activate at all.

I tested and it is activated with ENTER

 

[gtvkeith]

I apologise, it does work. I am having some further issues with the code in my ComboBox. But I can tell this code is working.

Thankyou very much both of you for your help.