How to set the value of an embedded combo box

Hi all,

I have a workbook from an external party (let's call it wb_Client for reference purposes) containing two embedded combo boxes. The combo boxes show up in the formula bar as =EMBED("Forms.ComboBox.1",""). It seems that these combo boxes have their own name because in the spot where you normally see the cell ID (e.g. B14), I now see names. Combo box 1 is called "cmb_Fruit", and the second combo box is called "cmb_Country". Both combo boxes have drop down lists. The first combo box has the choices "Apple", "Orange" and "Pear". The second combo box has the choices "UK", "US" and "France".

Normally, if I open wb_Client (simply by double clicking on the file) the values of the combo boxes are "Apple" and "UK" by default. Suppose that, for example, cell "B10" represents the unit price, and that this number is 0.80. Next, if I change the combo boxes to "Pear" and "US", cell "B10" updates to 0.50. And the 0.50 is what I am interested in.

I only need this process to be automated. Thus, using my own workbook and some coding, I need to open wb_Client, set the value in the drop down list of combo box 1 to "Pear" and in the drop down list of combo box 2 to "US". Maybe I need some code to update/calculate the worksheet, and then grab the value from cell "B10". So the main problem is how to set the value in the combo boxes. How do I do this?

I was given the suggestion:

Please Login or Register  to view this content.

But it says that variable cmb_Fruit is not defined. I am accessing the workbook as:

Please Login or Register  to view this content.

I also tried

Please Login or Register  to view this content.

but this isn't working either since listindex is not a property of Range.

Any suggestions?

Cross posted here.