Combo Boxes not displaying Data

[guitar713]

I have 2 forms.  The first form has drop down boxes and they work well..    I have a button in call and open the second form.  The second form opens and the first form remains open, which is what I want; however, the combo box on the second form is not displaying the data.  I have searched for answers and found none.  Can anyone help me get the data to show on the second form?  Yes, the second form is linked to the excel sheet just as the first form is.  Followed same procedure as the first form box.  Please help, this is driving me crazy.  Thanks much.

[#2]

@guitar713 

It sounds like the issue might be related to how the combo box in the second form is populated with data. Here are a few steps you can take to troubleshoot and fix the issue:

1. Check Data Source: Ensure that the combo box in the second form is linked to the correct data source in your Excel sheet. Double-check the range of cells or named range that you have specified as the data source for the combo box.
2. Verify Data Range: Make sure that the data range specified for the combo box includes the correct data and that there are no empty cells or errors within the range.
3. Refresh Data: If the data in your Excel sheet has been updated since you opened the second form, you may need to refresh the data source for the combo box. You can do this programmatically using VBA code or manually by closing and reopening the form.
4. Ensure Visibility: Check the properties of the combo box in the second form to ensure that it is set to be visible. Sometimes, if the visibility property is accidentally set to false, the combo box may not display any data.
5. Debugging: Use VBA debugging tools to check if there are any errors occurring when populating the combo box with data. You can set breakpoints in your code and step through it to see if there are any issues.
6. Recreate Combo Box: If all else fails, try deleting the combo box in the second form and recreating it from scratch. Sometimes, there may be hidden issues with the combo box properties that are difficult to identify.

If you're still experiencing issues after trying these steps, please provide more details about how the combo box is populated and any relevant VBA code that you're using. This will help in providing a more targeted solution to the problem. The text was created with the help of AI

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

[#3]

@NikolinoDE   Thank you for replying.  I have tried all the above.  Still no luck.  I was wondering if it has to do with a bug in software?  In that you can't have 2 forms open at once?  The strange thing is, I tested the combo box by moving it to the first form, and it works fine.  when I move it to the second form, I don't see the data.  Any other ideas?  Thanks.

[#4]

@guitar713 Having two userforms open at once shouldn't be a problem. Glitches can occur, though, especially with Excel for MS365. How exactly are you populating the combo box? Did you hard code the worksheet range directly in the RowSource property of the combo box? Did you define a name for the worksheet range, as shown in the example below?

ComboBox RowSource Property

Alternatively, you can also try leaving the RowSource property blank, then populate it on the fly in the UserForm_Initialize procedure. For example:

Private Sub UserForm_Initialize()
    Me.ComboBox1.RowSource = "lstStatus"
End Sub

Or, by looping through the items in the range:

Private Sub UserForm_Initialize()
    Dim rg As Range, i As Long
    Set rg = Range("lstStatus")
    For i = 1 To rg.Rows.Count
        Me.ComboBox1.AddItem rg.Cells(i, 1).Value
    Next i
End Sub

These are just a few basic examples. I've tested them all with two simple userforms (shown in the screenshot above) and did not have any issues. If you are still unable to get it to work, please consider sharing more details, such as the code you are using in the each of the userforms, especially the code that opens the second userform, as well as any code currently being used in the UserForm_Initialize procedure of the second userform.

[#5]

So the way I am calling the second form is to double click on a text box. I use the following code:
----------------------------
Private Sub txtCURRENTPeoplesoft_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmPeopleSoft.Show vbModeless
frmPeopleSoft.Enabled = True
End Sub
----------------------------------
But when I put the code that you recommended into the form 2 as follows:
----------------------------------
Private Sub UserForm_Initialize()
frmPeopleSoft.Enabled = True
Me.ComboBox1.RowSource = "Table3"
End Sub
-----------------------------
then I run my code, I get the following error

Run-time error '380':
Could not set the RowSource property. Invalid property value.

Then when I click on the debug tool, it goes to

Private Sub txtCURRENTPeoplesoft_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmPeopleSoft.Show vbModeless ---- this one is hightlighted as errored???

If you can follow my logic, this is what is happening.

thanks.

[#6]

I have tried to fill in the "RowSource" property as well in the properties dialog....
That did not work either...

[#7]

@guitar713 The error message you're getting is a pretty good indication that "Table3" does not exist as a defined name in the workbook. To verify the name of the table you want to use as the RowSource, select any cell within the table, then go to the Table Design tab on the ribbon. Copy the Table Name and paste it into your code to ensure that it's spelled correctly.

Table Design > Table Name

[#8]

I just confirmed it,, I do have the correct table called out,,, so I was curious,, I tried to paste another group of cells I know works from Form 1 into the "RowSource" area,, and that one also did not work. I know it works in Form1 but not Form2, still no data is showing up.

[#9]

@guitar713 Just as one last troubleshooting step, are you able to open the second userform on its own? From the Visual Basic editor, if you double click on the userform name in the project pane to view it, then press F5 on your keyboard, does it open up with the combobox populated? At this point, without working on your file directly, I'm out of ideas.

[#10]

YES, THAT WORKS.....

[#11]

@guitar713 So that begs the question... how exactly did you "link" the userform/combobox to the worksheet? In your original post, you stated the following:

"Yes, the second form is linked to the excel sheet just as the first form is. Followed same procedure as the first form box."

Please describe the procedure you followed. I'm confused, because after trying my suggestions, you then stated:

"I have tried to fill in the "RowSource" property as well in the properties dialog... That did not work either..."

The RowSource property is the only way that I'm aware of to "link" a combobox to a range in the workbook. If you didn't set the RowSource, and you're not populating the combobox via a loop, then what procedure did you use?

To populate a combobox via looping through the column of an Excel table, the code would look something like this:

Private Sub UserForm_Initialize()
    Dim tbl As ListObject, i As Long
    Set tbl = Sheet1.ListObjects("Table3")
    For i = 1 To tbl.ListRows.Count
        Me.ComboBox1.AddItem tbl.DataBodyRange(i, 1).Value
    Next i
End Sub

Note: Sheet1 is the CodeName of the worksheet where the table resides, and the column number used in this example is 1. Adjust as necessary.

You may also take a look at the sample file I've been testing these procedures with and compare the setup with yours to see if anything jumps out (see attached).

[#12]

I did use the RowSource in my properties in the 2nd form. In fact, I thought the link I used first in the 2nd form was wrong, so I changed the link in the 2nd form to a similar one I used in the 1st form. When I say link, I am referring to only the properties, I did not write any special code. Both links are not picking up when I open the 2nd form. It almost like it is not seeing the excel rows at all. However, when I drop down the combo box in the VBA, and the f5, I do get the menus populated, but not when I run the form in regular mode. If that makes sense.

[#13]

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

[#14]

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

[#15]

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.

[#16]

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

Note: 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!