pick up a cell data from a drop-down combo list

Dear All:
I have a drop-down combo list which has 17 rows and 8 columns. How can I pick up any data in this list by clicking on the data? I am attaching my file here and I will appreciate it so much if anybody can help me on this.
[email protected]

It would help if you could explain exactly what it is you want to achieve

Ed

Originally Posted by voodoofox

Dear All:
I have a drop-down combo list which has 17 rows and 8 columns. How can I pick up any data in this list by clicking on the data? I am attaching my file here and I will appreciate it so much if anybody can help me on this.
[email protected]

You can use the combobox1 Change event.

You have set the BoundColumn property of your combobox1 to 1, which means the value of the combobox will be the value in the first column of your range 'tanksize'. i.e. 11, 14, 17....etc. You can therefore incorporate this in a Vlookup within the macro to populate whichever cells you wish with the data from the appropriate row of your tanksize table. So in design mode of the Excel sheet right click on the combobox and choose 'View code'

Now enter the following within the Sub ComboBox1_Change event"

Please Login or Register  to view this content.

This will populate C1:I1 on sheet1 with the appropriate values from your tanksize table. Adjust the cell references and/or macro as appropriate.

HTH

Hi, Richard:
Thank you so much. The code generated the result which is pretty close to what I need except that it populates ALL data of the row highlighted by my mouse, but what I need is to populate only ONE data selected by my mouse or pointed by my mouse.
It sounds to me that the position of the mouse should be auto-detected for the VBA code to decide the right x-value or the right column number, then the vlookup function will, based on the iRow and x-value, find the right data for me from the Range "tanksize".
Could you help me on this point?
Thank you again.

Thanks for PM but better to keep discussion in the forum.

VB is not my area but I am sure that you could achieve what you want through formulae - hence my request for greater detail of what you want to achieve.

If you want a VB solution, suggest you work on the code that Richard offered.

Ed

Originally Posted by voodoofox

Hi, Richard:
Thank you so much. The code generated the result which is pretty close to what I need except that it populates ALL data of the row highlighted by my mouse, but what I need is to populate only ONE data selected by my mouse or pointed by my mouse.
It sounds to me that the position of the mouse should be auto-detected for the VBA code to decide the right x-value or the right column number, then the vlookup function will, based on the iRow and x-value, find the right data for me from the Range "tanksize".
Could you help me on this point?
Thank you again.

Hi,

The Mousedown event doesn't capture the position of the mouse in a range when clicked, just the state of the mouse button, i.e whether Shift or Alt or Ctrl etc. were also part of the mouse click.

The click and other events can return one value from the Combobox, and at the moment that comes from the first column of the tanksize range, but it could be set to any value.

I don't know of any way to identify a column position from the mouse click, which would be necessary to achieve the result you want. The only way I can think of doing this would be to have a second combo box that allowed you to pick the relevant column. Then a combination of both comboboxes would allow you to pick a specific cell value.

How do you wish to proceed?

Regards

Richard and Ed:
Thank both of you for the help. The idea of having the second combo box will definetely solve the problem, and I can handle it.
Thank you again for saving my time by telling me there dose not exist a way for me to do that without the second combo list, although I really hope there exists such a way.

Originally Posted by voodoofox

Richard and Ed:
Thank both of you for the help. The idea of having the second combo box will definetely solve the problem, and I can handle it.
Thank you again for saving my time by telling me there dose not exist a way for me to do that without the second combo list, although I really hope there exists such a way.

Hi, there is another way which was staring me in the face, if you are prepared to make sheet 2, with the tanksize range, visible. The following Sheet selection change event will allow you to click any cell in the tanksize range, and the cell item selected will appear in cell B70 - although of course that could be changed to anywhere. This loses your option of selecting from a combobox but you may be able to work with this. I'm also attaching the workbook which contains this.

HTH

Please Login or Register  to view this content.

Richard:
Thank you so much for your great VBA knowledge. All of your input is really helpful for my job.
Duo to the size of that tanksize table, currently I still prefer using two combo boxes to solve the problem.
Thank you again and my best regards.