PDA

View Full Version : Populating combo box using dynamic range - run time error 1004



ncortez
06-29-2012, 04:50 AM
Hello,

I am trying to populate a combo box based on a dynamic range on a worksheet. What I have right now works about 40% of the time, but then the other 60% I am getting a:

Run-time error '1004': Method 'Range' of object'_Worksheet' failed.

Here is the code I have:

Private Sub cmdEdit_Click()

Dim MyList As Range
Dim rngItems As Range
Dim ws As Worksheet

Set ws = Worksheets("R&O_Form")

Set MyList = ws.Range("C2", Range("C65536").End(xlUp))

With Me.cbo_item
For Each rngItems In MyList
If rngItems.Value > vbNullString Then
Me.cbo_item.AddItem (rngItems.Value)
End If
Next rngItems
End With

End Sub

After the lines I just posted, i do some formatting to the controls (i.e. change the background color of the combo boxes etc), however this isn't until after and the code gives me the error at the bolded line above.

I've searched through the forums as well as the internet and have not yet found an answer. It seems weird that it works at times, but not at others and I'm not sure what could be causing this. Could anyone please help?

snb
06-29-2012, 05:10 AM
use only


Private Sub cmdEdit_Click()
cbo_item.list=sheets("R&O_Form").columns(3).specialcells(2).offset(1).specialcells(2).value

End Sub

ncortez
06-29-2012, 05:48 AM
This gives me another error:

Run-time error 381: Could not set the List property. Invalid property array index.

snb
06-29-2012, 06:34 AM
remove anything in the property rowsource.

Please post a sample workbook.

Tinbendr
06-29-2012, 09:29 AM
Qualify this range also.

Set MyList = ws.Range("C2", ws.Range("C65536").End(xlUp))