Thread: using a worksheet range to populate a combo box in excel

Thanks Ivan,

I can see you have put some thought into the code your supplying me and
I appreciate your help.

I'm not sure how to initialize the combobox.

This is the only code I have!

Private Sub UserForm_Initialize()

txtQuantity.Value = "1"
cboPartsused.Value = ""
cboPartsused.SetFocus
spnButton1.Min = "1"

End Sub

I would appreciate your help here!

Nearly done now (I hope)!

Thank you,

Kev

Hi Kev,

sorry for replying so late, too busy now.

in code we did together:

Dim rng As Range
Set rng = worksheets("temp parts").Range("a2")
Me.cboPartsUsed.List = rng.parent.Range(rng.Address,
rng.End(xlDown).Address).Value

replace "Set rng = worksheets("temp parts").Range("a2")" with "Set rng
= worksheets("temp parts").Range("a2..d2")" probably - "a2..d2" is the
location of first data in "temp parts" sheet.

Set columncount in properties of combobox cbopartsused to 4. Then your
combobox will show four columns of data. If you don't want some of them
to display, set accordingly columnwidths in properties of cbopartsused.

Regards,
Ivan

Ivan,

Don't know if you will get this, I used the reply button as usual, but
have been directed elsewhere!

Dont worry about taking time over this, I dont expect an immediate
response, seeing as though I'm getting free consultancy here - I'm
gratefull for whatever information I get, and I respect the fact that
you must be doing something full time elsewhere.


Okay, it nearly works apart from one or two things.

If I leave the combo box blank, I get, "runtime error 381, could not
get the list property, invalid property array index".

The cell a24 was selected as the first cell. I have corrected this
changing the reference in the code to a22. Also the data on finantial
copy needs to go from a23 onwards, so I changed the reference here from
a1 to a22 as well.

The code works on the financial copy twice only. What happens here is
cell a23 gets the first part, cell a24 gets the next, from then on cell
a24 updates instead of moving onto cell a25.

There is nothing happening in the financial copy at all!

Just a minor bug I know, but I cant get my head round it.

Thanks again,

Kev