Forum Discussion
Combo Box dynamic code not working
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 With
For 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.
djclements Ild like to thank you for guidance on this program. What I am very confused with is how the references work with the forms and spreadsheets, how it pulls in the data from the excel files and puts them into the forms. This has been and continues to be problematic with this project. Again, I am having problems with the lstDatabase in module mdDataentry. let me know why this works in another project that I pulled this from and not in this one... when I change the wording to what you see in the file I sent you, sometimes it works and other times it does not work. Very confused. Can you help some more and set me on a road to know how to reference the files to fill in the database at the bottom of the form... and FYI, I want to use the REQUEST FORM_GATE1 to get my data from to populate the database in the form. Thank you very much.
