Combo Box dynamic code not working

[guitar713]

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

[#2]

@guitar713 

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?

[#3]

I do not get any specific error message. It just stops at this point in the code and says to debug....

[#4]

@guitar713 

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?

[#5]

@Hans Vogelaar   here is a copy of my code...   open it up by pressing the ECO button,  then goto the select in the URGENCY section, this is where the problems are located.  

[#6]

@guitar713 

Try this:

Private Sub cmbURGENCY_Change()
    Select Case cmbURGENCY
        Case "HAND CARRY - HIGH – 1 Bus Days"
            FrameURGENCYOVERALL.BackColor = RGB(245, 99, 75)
            Sheet4.Range("C2", Sheet4.Range("C" & Rows.Count).End(xlUp)).Name = "Dynamic"
        Case "FAST - MEDIUM – 2-3 Bus Days"
            FrameURGENCYOVERALL.BackColor = RGB(255, 251, 0)
            Sheet4.Range("D2", Sheet4.Range("D" & Rows.Count).End(xlUp)).Name = "Dynamic"
        Case "NORMAL - LOW – 5 Bus Days"
            FrameURGENCYOVERALL.BackColor = RGB(155, 247, 141)
            Sheet4.Range("E2", Sheet4.Range("E" & Rows.Count).End(xlUp)).Name = "Dynamic"
        Case Else
            FrameURGENCYOVERALL.BackColor = vbWhite
            Sheet4.Range("F2", Sheet4.Range("F" & Rows.Count).End(xlUp)).Name = "Dynamic"
    End Select
    Me.cmbCARRYREASON.RowSource = "Dynamic"
    cmbCARRYREASON.ListIndex = 0
End Sub

[#7]

@Hans Vogelaar   I thought I had it working, then is crashed again.  This program seems buggy.  It crashed after I saved a copy of the file.

[#8]

@guitar713 

When does it crash? I couldn't reproduce the crash in a quick test.

[#9]

when I first open it up and hit the ECO button
I

[#10]

@guitar713 

I see. The problem doesn't occur if you leave the workbook visible. Would that be OK?

[#11]

I dont want the spreadsheet visible. I want to have the option to look at spreadsheet when we want to. I am not trying to create confusion. Having the spreadsheet open and the form gets confusing. I want it to be one or the other, not both at the same time. If that makes sense.

[#12]

@guitar713 

OK.

[#13]

@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.

[#14]

now I am getting the following error.

@djclements 

[#15]

@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.

[#16]

@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.

[#17]

I wanted to add the problem can be found by hitting the "RESET" button

[#18]

@guitar713 Again, the majority of the problems you're experiencing are due to the application window being hidden. Setting the RowSource property of a List Box or Combo Box when the workbook is hidden is not as simple as it is when the workbook is visible. In the Reset_Form sub routine, try using the same Worksheet.Range.Address method I outlined in my previous replies, with the optional [External] argument set to True:

Sub Reset_Form()
    Dim irow As Long
    With frmDataEntry
    'assigning rowsource to lstDatabase listbox
        .lstDatabase.ColumnCount = 12
        .lstDatabase.ColumnHeads = True
        .lstDatabase.ColumnWidths = "40,80,80,40,45,70,80,0,0,0,0"

    ' identify the last non-blank row in database sheet
        irow = Sheet1.Range("A" & Sheet1.Rows.Count).End(xlUp).Row
        If irow > 9 Then
            .lstDatabase.RowSource = Sheet1.Range("A9:L" & irow).Address(External:=True)
        Else
            .lstDatabase.RowSource = Sheet1.Range("A9:L9").Address(External:=True)
        End If
    End With
End Sub

Note: I changed the starting row in the above code from 2 to 9 because that's the row in your worksheet where the database starts. Also, some of your headers in row 9 (column A, B and C) have been merged with cells in row 8 (using Merge & Center)... this will have an undesirable affect to the appearance of the List Box, since you've set the ColumnHeads property to True. You should "unmerge" those cells and put the column headers in row 9 only.

You could save yourself a lot of headaches by leaving the workbook visible. The same effect that you're going for could be achieved through alternative means. For example, you could designate a single worksheet to be the "landing page" or "navigation page" when the workbook is opened, and every other worksheet could have their Visible property set to xlSheetVeryHidden (so they cannot be unhidden manually by the user). The "navigation page" worksheet could easily be designed just like your current UserForm1, with a button to launch the frmDataEntry user form. This would allow you to keep the workbook visible while hiding all of the other worksheets. Just a thought...

[#19]

after playing with this, I figured out another method,,, similar to a refresh,, when reseting the form, I make it visible for a brief time, then rehide,,, it flashes briefly in background, but not so annoying.