Forum Discussion
guitar713
Apr 09, 2024Brass Contributor
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...
guitar713
Apr 11, 2024Brass Contributor
I wanted to add the problem can be found by hitting the "RESET" button
djclements
Apr 13, 2024Bronze Contributor
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...
- guitar713Apr 13, 2024Brass Contributorafter 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.