Combobox change value

[ignasis]

Hello again,

I think my problem is quite easy to understand in the attached file.

What I need is to select an option from combobox (for example "meat") then write quantity (2kg) and prize (10€). After this click on Save button and values go to the appropiate excel cells,

Here comes my problem: I would like to follow with the combobox list, so after meat I will choose "fish" and again fill quantity and prize, click on save and send that values to cells.

View attachment exampleCombobox.xlsm

Hope I explained properly,

Thank you!

 

[PhilB1]

Hello Ignasis

Try the attached & see if it's what you need. You can add or delete rows to the range on column B & the code will adjust for it and you can enter the items in the dropdown box in any order.
Have a play
Cheers

 

[ignasis]

wow PhilB1, thank you very much!

I wouldn't imagine it would be such a "long" code just for that

Thanks for your time and effort! It works perfectly, as I needed

 

[snb]

To, to much code for a simple task.

e.g.

Private Sub UserForm_Initialize()
  cboChooes.list=split("Meat Fish Vegetables Chocolate Rice Fruit Cereals")
End Sub

 

[ignasis]

snb, what does your code do or replaces in the previous?

thanks!

 

[snb]

Exactly what it describes:

Private Sub UserForm_Initialize()
   cboChoose.list=split("Meat Fish Vegetables Chocolate Rice Fruit Cereals")
 End Sub

 

[ignasis]

well, I see the split option but I never used it and don't know how to link the split with my textboxes.

it might be less code, but I don't how to use it

I succedeed in adding PhilB1 code to my project and it's working pretty good!

 

[PhilB1]

snb's code will replace the code you have for the Userform-Initialise code. It will populate your dropdown box exactly the same, but with less code.

 

[snb]

see the attachment for an illustration

 

[PhilB1]

snb
I've not used lists before, I think it's worth looking into after seeing your code

 

[snb]

You might have a look at:

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

 

[ignasis]

I actually had this link as favourite

I liked your really short code, but it's hard for me to understand it 100%.

thanks for all the info both of you!!

 

[PhilB1]

Thanks snb, You've obviously been doing VBA for a while, far more advanced that I lol. I've bookmarked the page

I agree with ignasis in the being able to read the code. I've worked out what is doing what by putting some variables in there to store values so I can see what's happening as I step through the routine.

snb..
Can you explain why you used "If C_0.ListIndex > -11 Then" please. I changed it to "If C_0.ListIndex > 0 Then" (both without quotes) and it worked exactly the same. I can't see what the -11 is for

Cheers

 

[snb]

That's a typo (2*)

should be :

If C_0.ListIndex > -1 Then

NB. The main principle is: reduce the interaction with the worksheet to a minimum.
Store all - changed- values in the combobox/listbox and write those to the worksheet only if you have decided to finish your work.