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?
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?