User form 1

[tdlynn]

Hi all, I have made an excel workbook which has worked quite successfully so far (with a few usual hiccups of course!) but have come across a problem. I know the basics of VBA and the use of user forms (applying buttons to a user form to make a box to ask if you want to continue etc.)

The problem I am having is that I want a user form to show a drop down list of a range of cells. I.e. you click the arrow to drop down the list and the list displayed corresponds to a list of cells in another sheet. (e.g. in the sheet Suppliers then the range C8:C60). Ideally after this i would like it to copy the corresponding cell in the "A" column and put it into cell "D8" in the sheet "Add new purchase"

This seems complicated but I am sure it is reasonably simple and I hope someone can help.

Thanks
Tom

 

[RobBroekhuis]

Just add a combobox to your userform, and set the rowsource property of the combobox to the range containing the display values for your list (e.g. "Suppliers!C8:C60&quot.
In the combobox_change event handler, use the listindex property to locate the cell you want to copy, e.g.

with sheets("MySheet&quot
.range("A8&quot.offset(combobox1.listindex,0).copy .range("D8&quot
end with

You'll have to perform a check to make sure a selection is made (i.e. listindex<>-1)
Rob

 

[tdlynn]

Thanks for the reply. The first bit is great but the second isn't working, even with me trying to tinker with it a bit.

It needs to select the sheet &quot;suppliers&quot; find the selection we have just made in the combo box, and find the number which is in the cell two columns to the left of it. Then it needs to copy it into the sheet &quot;add new purchase&quot; and cell D8.

Thanks
Tom

 

[RobBroekhuis]

The selection you just made would be in column C, right? Then two columns to the left of it is column A. Method 1 below copies the cell in column A corresponding to the selection made in the combobox to the cell you specified. Note that if you want just the cell's value, it's better to use the second method below

method 1.

sheets(&quot;Suppliers&quot.range(&quot;A8&quot.offset(combobox1.listindex,0).copy sheets(&quot;Add new purchase&quot.range(&quot;D8&quot

Method 2.

sheets(&quot;Add new purchase&quot.range(&quot;D8&quot = sheets(&quot;Suppliers&quot.range(&quot;A8&quot.offset(combobox1.listindex,0)

Try either approach and see if it works for you.
Rob

 

[tdlynn]

Hey,

Thanks for the reply again, that one worked.. Well done and thanks again.

Tom