-
Populating combo box using dynamic range - run time error 1004
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:
[VBA]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[/VBA]
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?
-
use only
[vba]Private Sub cmdEdit_Click()
cbo_item.list=sheets("R&O_Form").columns(3).specialcells(2).offset(1).speci alcells(2).value
End Sub[/vba]
-
This gives me another error:
Run-time error 381: Could not set the List property. Invalid property array index.
-
remove anything in the property rowsource.
Please post a sample workbook.
-
Qualify this range also.
[vba]Set MyList = ws.Range("C2", ws.Range("C65536").End(xlUp))[/vba]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules