Forum Discussion
Combo Box dynamic code not working
Hello,
I have tried to use a code that works in one program but not another program to dynamicly populate comboboxes. Can someone help me resolve this. The code is as follows:
With frmDataEntry
Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp)).Name = "Dynamic"
.cmbCARRYREASON.RowSource = "Dynamic"
End With
thanks much
What exactly is the problem?
- Do you get an error message? If so, what does it say?
- Does the code run, but without setting the row source of the combo box?
- Does the code run, but it sets the row source incorrectly?
- Something else?
- I do not get any specific error message. It just stops at this point in the code and says to debug....
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
guitar713 Since the application window is hidden, you need to explicitly reference the worksheet. For example, Rows.Count must be changed to Sheet4.Rows.Count. Also, when setting the RowSource, use the Worksheet.Range.Address property with the optional [External] argument set to True:
Sheet4.Range("C2", Sheet4.Range("C" & Sheet4.Rows.Count).End(xlUp)).Name = "Dynamic" frmDataEntry.cmbCARRYREASON.RowSource = Sheet4.Range("Dynamic").Address(External:=True)This can also be done dynamically without using the "Dynamic" named range:
frmDataEntry.cmbCARRYREASON.RowSource = Sheet4.Range("C2", _ Sheet4.Range("C" & Sheet4.Rows.Count).End(xlUp)).Address(External:=True)Alternatively, you could set the List property of the combo box, rather than the RowSource:
With frmDataEntry.cmbCARRYREASON .Clear .List = Sheet4.Range("C2", Sheet4.Range("C" & Sheet4.Rows.Count).End(xlUp)).Value End WithFor more information, please refer back to my last response to your previous question found here: Combo Boxes not displaying Data.
guitar713 It looks like you added Sheet4 to Rows.Count, as suggested, but missed my second suggestion of using the Worksheet.Range.Address property to set the RowSource. Change the highlighted line of code shown in your screenshot to the following:
Me.cmbCARRYREASON.RowSource = Sheet4.Range("Dynamic").Address(External:=True)Personally, I would use the alternative method shown in my previous reply, setting the List property instead of the RowSource, but that's just me.
