Activate combobox on active cell instead of doubleclick

[Berna11]

Good morning, I would like some help to modify a VBA code, if possible, that activates in column B sheet1 of the attached file, the comboboxes when the cell is active are now activated with doubleclick.

Also, I would like to have the data read from the Data sheet and remove it from the sheet1 column I
thank you

Please Login or Register  to view this content.

[Keebellah]

Hi, try the event Worksheet_Selection_Change (check the syntax )

[Berna11]

Hello
Thanks for the reply
but, I wanted to ask you if you can better explain how and where
use your indication.
Nin I am expert in VBa would you do me a favor to tell me how to edit
Thank you

[Keebellah]

Sorry, no Excel at hand at this moment. but YOU have the code ?????

[Keebellah]

Forget the code you have there. it's unnecessary just a table (Prodotti) and and a Data Validation list

[Berna11]

hank you
yes, I can do that.
I put the comboboxes because, starting to type a word,
the rest appears automatically, without having to choose it from the drop-down menu.

[Keebellah]

In that case I would use a userform with a textbox to type and listbox that opens when you right-click one of the cells in that column

[Berna11]

Hello,
it seems too cumbersome.
I need something very quick to insert words / names in column B that I already have in a list and,
as soon as I start typing the word / name and find the one you are looking for, enter or tab and go
to the cell below for a new always quick and fast insertion.
Enter word start -> found -> enter -> step ad to cell below.

That of the initial file could be useful if instead of the double / click to open the combobox
it opens on the active cell in column B and, moving with enter opens the one below
or where I click with mouse always and only in column B.

[Keebellah]

Let me code this and show you my idea.
It only takes a second to select and click or a little longer to type, filter and select

[Keebellah]

Try this, In my opinion this is workable

[Berna11]

I tried what you proposed to me.

It's not bad as a good idea, but in this file I would need something fast.

Open the userform with the right mouse button to each cell, even if a simple click,
it is not practical, it would be better if it also opened by itself (as I had asked for the combobox)
when I go to the next cell or when I click on it a cell in column B.

Another problem, once you start writing in the TextBox and, having found the product,
you still have to click on the ListBox to bring it to the sheet and it does not go with enter.

I don't know if it's possible to apply these variations ...

[Keebellah]

With Excel everything is possible.
Start practicing, modify the code and see what you can do, Add the worksheet_selection Change event , many more.
If you are in that need of speed, well... happy coding.
The double click you had was nothing good as was your dropdown list

[Keebellah]

I removed all the formulas and in the worksheet Elenchi there is a tabel named tblProdotti
First column is the name and the second the category.
You no longer need the formula in Column A to find the 'REPA'

You can even add an extra text box to filter for only Verdura or Fruta etc.

[Berna11]

thank you for your effort you have eliminated the formulas but,
these did not waste time, as mentioned above I would have preferred that, once the product was found, it would be sent to the sheet once it was sent.

Anyway I did some experiment of my file
I'll show you what came out.

Operation is how I wanted it.

All that is left is to be able to read the products from column L of the Dati sheet
instead of from column I scheet1. I was unable to make this variation.
Can you help me make this change?

[Keebellah]

I'll take a look and see what I can do for you

[Keebellah]

I don't know where you defined the list but all you have to is change it to Nomi

[Berna11]

Hello
I have validated data = Names
but when you open the combobox nothing appears

There should be something about the code that I don't understand.

.ListFillRange = ws.Range (str) .Address

[Keebellah]

Please Login or Register  to view this content.

[Berna11]

No, it doesn't work.
combobox rdoes no search

[Keebellah]

Well, then you have to check if your range is valid, maybe use list source or similar
I have no idea what you’re doing now and how the data looks
I never use activex controls

[Berna11]

Hello
the post # 14 file works and the data validation data takes them
from sheet sheet1 column i.
I wanted to delete this I column and move the data to the L column Dati sheet
I used in data validation
1 - the reference to the Dati sheet = Dati! $ L $ 5: $ L $ 100
2-- by assigning a name to this range of Nomi in the Name Manager and then reported in the data record.
But, in both cases, it doesn't work and I don't understand why.
Maybe there is something in the code that I don't understand ....

[Keebellah]

I'm not going to discuss or even try to understand why you have this complicated method for 'speed'

In your code you will have to make sure that the ws points to the worksheet named Dati , I see that the ws is set to active sheet and THAT is NOT Dati

[Berna11]

No, no problem and no discussion
I also tried to change from active sheet to Dati sheet
but it still doesn't work.

For the speed of the method, if you try it, you will see that it is not necessary to use the mouse
(it is a file for a blind person and can only use the keyboard) and when it starts writing and decoding the word the speech synthesis reads what it has written and found so as to realize if it is correct or not.

So you will understand that the drop-down menu cannot be used to open the curtain with the mouse or click on the product you need.


I thank you for your effort and time that you have dedicated to the end. I will keep it in this way maybe I move the data to the right on the same sheet.

[Keebellah]

Okay, great, e bettee explanation makes things clear as to why you want something in a particular way