Forum Discussion
Combo Boxes not displaying Data
guitar713 Fair enough. I've tried everything I can think of but was unable to duplicate the problem. There may be more to your situation than I'm currently aware of. If you want me to take a look at the file, but don't want to share it publicly, you can send it in a private message. Just click my profile link in this thread, then click the Message button at the top and Browse to attach the file.
guitar713 After taking a look at your file, it seems the reason for the RowSource property not working as expected is because the Application is not visible when the userform is loaded, due to the way you have it setup to hide the workbook when interacting with the user forms (ThisWorkbook.Windows(1).Visible = True). As such, the best way to load all of your combo boxes is the leave the RowSource property blank, and populate them in the UserForm_Initialize procedure with the following simplified method:
Private Sub UserForm_Initialize()
Me.ComboBox1.List = Sheet4.ListObjects("Table3").DataBodyRange.Value
End Sub
Also, when using the DblClick event to show the second userform (vbModeless), the first userform will still remain on top. To stop this from happening, and keep the second userform in the forefront, use Cancel = True as follows:
Private Sub txtCURRENTPeoplesoft_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmPeopleSoft.Show vbModeless
Cancel = True
End Sub
I hope that works as expected. Cheers!
guitar713 Good question. When using the RowSource property, I had varying results for the first userform, but consistently could not get it to work for the second userform. Here's what I found (just through testing):
When using the Defined Names, URGENCY and UPDATYP, as the RowSource property for the two combo boxes on frmDataEntry, neither one worked.
When including the Sheet Name in the RowSource property, 'FORM SUPPORT'!$B$3:$B$5 or 'FORM SUPPORT'!URGENCY and 'FORM SUPPORT'!$C$3:$C$14 or 'FORM SUPPORT'!UPDATYP, it worked for the combo boxes on frmDataEntry, but the same method failed for the second userform, frmPeopleSoft, when using either 'FORM SUPPORT'!$D$3:$D$10 or 'FORM SUPPORT'!$SOFTNAME. I'm afraid I can't explain this inconsistent behavior, but I can say for sure that it's related to the Application Window being hidden. When I commented out the code to hide the Window, the RowSource property works as expected for all combo boxes on both userforms, by simply referencing the Defined Names.
Due to this inconsistent behavior when the Application Window is hidden, I would still recommend leaving the RowSource property blank for all combo boxes and populate them when the form is loaded. Also, you would still need to explicitly reference the Sheet Name or CodeName of the sheet in which the named ranges reside. So, in the case of frmDataEntry, use:
Private Sub UserForm_Initialize() Me.cmbURGENCY.List = Sheet4.Range("URGENCY").Value Me.cmbUPDATEType.List = Sheet4.Range("UPDATYP").Value End SubNote: just a heads up, since your code is using frmPeopleSoft.Hide and frmDataEntry.Hide, the forms are still loaded when you go back to the "Excel View". This means that if you then jump over to the VBA editor and make a change to either of the combo boxes, then jump back over to the worksheet and click the "ECO" button to bring up the "Form View" again, the changes have not yet taken effect, as the forms were never unloaded / reloaded. This can lead to confusion, thinking the changes you made either worked or did not work. When making / testing changes in the development stages, you would need to use Unload frmPeopleSoft and Unload frmDataEntry to make sure the forms are being reloaded from scratch. You can do this in the Immediate Window of the VBA editor (View > Immediate Window or Ctrl+G).
I hope that helps!
- One more question,,have not tried what you recommended yet, but if the workbook is hidden, why does the first userform recognize the workbook, but the second userform does not recognize the workbook. Should we have the same issue in the first userform as well? any thoughts? Thanks.
