VBA: Making ComboBox Open on Selection

[AliGW]

Good morning, all!

I've successfully managed to deploy the script below (from the Contextures site) in my workbook, and it works beautifully, but I was wondering if it is possible to add two small tweaks:

1. Ideally, instead of having to double click the cell containing the validation list to make the combobox appear, I'd like to single click. Having Googled a bit first, I am not sure if this is possible, but thought I'd ask.
2. When the combobox is selected, I'd like it to drop down automatically, revealing the list.

Here's the code (slightly adapted from the original to make it work in my workbook):

Please Login or Register  to view this content.

I'm attaching the sample workbook from the Contextures site, as mine contains sensitive data. Thank you for any advice.

[mehmetcik]

Please Login or Register  to view this content.

[AliGW]

Thanks. I will try to understand what you are telling me and implement it!

[Keebellah]

Ali, I think this is easier:

Please Login or Register  to view this content.

I'm no friend of the SendKeys but this is probably the only way to get it to work, no combobox, just the validation list
I've attached my version of you sample file

[AliGW]

Thanks, gents. Unless I'm missing the obvious, neither does what I thought I'd asked! They both open the validation list, not the overlying combobox. It's the combobox that I want to open when the user clicks on the cell.

I need to use the combobox because I want the list to change as the user types into the box.

If what I've asked isn't possible, it's no big deal.

[mehmetcik]

Please Login or Register  to view this content.

[AliGW]

Thanks, but just providing code without any explanation I am afraid is not great for me - I am no expert. When you provide code, perhaps you wouldn't mind just adding a line or two about what you've changed and why? I do want to learn as I go along. Thanks.

[AliGW]

No, sorry - I have looked at the latest code in post #6 and can't work out where it should go and what in the old code I should take out. Sorry, but I'm just not proficient enough to be able to use what you have given me without some instructions.

[mehmetcik]

This code replaces both your Selection Change and Your DoubleClick event code.

Please Login or Register  to view this content.

[Roel Jongman]

Hi Ali,

Towards your first question.. of when and where to show the combobox. This is down to the event macro you pick basicly there are 3 choices.

Worksheet_SelectionChange - This event macro is triggered by selecting a (different) cell
Worksheet_BeforeDoubleClick - This event macro will gets triggered by double clicking your mouse
Worksheet_BeforeRightClick - This event macro will gets triggered by right-clicking your mouse.

So depending on when you want the combobox to show, you can pick any of the three events

all you need to do is copy over the entire code from BeforeDoubleClick event to the SelectionChange event. And remove (or comment) out the ling that says Cancel = True because selection change does not have that preset. (Cancel = true is used in before double click to suppress the cell edit mode and in the rightclick event to prevent the menu from showing)

Please Login or Register  to view this content.

I saw you had some of the code already in selection change but it was missing some parts where the validation list is linked to it so there are values to show. But when you move the whole code then it works as intended. I attached the example with the working code. but basicly I did nothing else than copy paste the code to the selection event (and disable the cancel line)

For your second question, I do not know of any setting that can do that. but that might be my lack of knowledge although I think it does not exist as comboboxes are intended to be fixed on the sheet or a userform and have the arrow to indicate you can show a dropdownlist.

[AliGW]

Thank you both. I've been gardening, but will look again at this later today. I will let you know whether or not I manage to work it out! Thanks for the added information.

[AliGW]

Hi, Roel!

Towards your first question. of when and where to show the combobox.

Neither of my questions was about this, actually - sorry you got that impression. The combobox already appears where and when I want it to.

I will have a look at your workbook and see if it addresses the issues. I am afraid that Mehmetcik's doesn't appear to.

EDIT: OK - yours addresses the issue of the combobox appearing with just one click instead of having to double click - thank you. Is it possible to get the combobox to drop down (open) as soon as we click? I have seen combobox.dropdown online, but don't know where I would need to put this in the code.

[AliGW]

I've just one more thing that I'd like to be able to do here if possible. Can I get one cell (A31) always to show whatever is currently in the combobox whenever it is active and being typed into?

[Roel Jongman]

Ah, cool I did not know that .Dropdown
It is realtive simple to use. the combobox you use is named TempCombo and that name can also be used to handle the combobox settings. so the proper code is


TempCombo.Dropdown

I placed it after the combobox codeline where the shape is activated.
the cboTemp name that is given is part of the shape-objects and that type of object does not allow dropdown. so that cannot be used to handle the dropdown showing

Please Login or Register  to view this content.

[AliGW]

The drop-down works, however the range statement doesn't make anything appear in A31.

Another side effect now is that I cannot hit enter or tab to move out of the combobox.

[AliGW]

I have resolved the tabbing issue - I had accidentally introduced a typo in the sub in question, but that is now fixed.

I have decided not to bother with the mirroring of the combobox value in A31 for now.

Thanks again for all the help!

[AliGW]

Sorry - one further question. Is there a way to limit the appearance of the combobox to certain rows in the worksheet? Specifically rows 3 to 24. In other words, I only want the combobox to appear over data validation cells within those specific rows.

[Keebellah]

You can limit it using the target intersect like I used in my idea several post back

Please Login or Register  to view this content.

[AliGW]

I'm sorry - I have looked at the code in your earlier workbook, and you said that was for a solution without the combobox, which I don't want.

Where in the current code below should I add your new code snippet, please?

Please Login or Register  to view this content.

[Keebellah]

My answer was to give you the idea how to make something happen in a desired region using the Intersect.

The code you pasted, from which file is that?

[AliGW]

The code is from the workbook I am using (that I cannot share), but that is based on the code in the workbook I shared in the opening post.

Where in my code (or the code in the workbook in my opening post, if you prefer) would I need to add the code you have suggested in post #18, please?

[Keebellah]

Let me get back to you later, I'm late and have to go now.
I've also got some suggestions but let me think them over.

[AliGW]

OK. I'm at work now, so will look in later.

[Roel Jongman]

In the current SelectionChange event code there is a line that makes sure that this combobox only appears on cells with a Data validation of the list type
this is the codeline that does that (it is about halfway down the event code, just above the 'cancel=true disabled in my last post)

Please Login or Register  to view this content.

That means that on every cell on that worksheet with a Data validation of the type list the JuniorsCombo-box will appear.
If you only want to show it in a certain range of cells then you can add a line (or replace the current line with)


Based on the range B2:C11 in your example sheet you can add the following to the current line

Please Login or Register  to view this content.

this will limit the combobox to appear only in that cells if they have a cell validation list. I kept this part in to make sure that if in your real sheet there are blank cells or cells to fill without a datavalidationlist the box will not appear.
offcourse you will need to change the range of cells to fit your needs in the original sheet.

[AliGW]

Perfect, Roel - thank you once again. It's so simple when you know how (which, evidently, I don't). My workbook now works flawlessly.

Thanks again to all respondents.

[Roel Jongman]

Glad to have helped out and thanks for the rep.

[Keebellah]

I was called away and wasn't able to help out and just see that it's solved, but still I'd like to share this one.
You see, I'm not really a fan of ActiveX elements even if you can do nice things with them.
What I did is have a validation list in the worksheet ValidationLists and in sheet 1 in Column A1-A100 cells with data validation for the list in the other sheet.
I was playing around and needed code to append n a missing item to an existing validation list and thus this one.
Two ways, enter a value in E3 and if it it not present in the list it will be added, the list sorted and the data validation in A1-A100 updated.
Or you can double click a cell in column A (with the data validation) and enter a new value in the input box,

This was just for fun but maybe it can be put to good use