RowSource Runtime Error 308

[gemcgraw]

I'm not sure if my issue is using Microsoft Office Excel Professional 2019 or something else. However, I have a combobox (ComboBoxVendors) on a userform that I need populated with vendor names. I'm trying to set the RowSource value in VBA code and getting, "Runtime error 308. Could not set the RowSource property. Invalid Row property value." Below is the code I have set on the Private Userform_Activate subroutine:

Dim Source As String
    'Find the last populated row in the range.    
        NextRow = Sheets("Vendors").Range("B" & Rows.Count).End(xlUp).Row


    'Assign the complex RowSource string into one stringed value
        Source = "Vendor!B2:B" & NextRow
   
    'Set the RowSource property.
        UserForm1.ComboBoxVendors.RowSource = Source

I'm using this sub to load the values in the ComboboxVendors on the UserForm ahead of time. There have been numerous suggestion on MrExcel and other forums that suggest several variations of this; however, I still get the same error. The only way around the error is to use this code:


UserForm1.ComboBoxVendors.RowSource = "Vendors!B2:B5"


This resolves the error and the ComboBoxVendors values load as expected. However, the range "B2:B5" will grow. Thus, I don't want to "hard-code" the range. And, I don't want to simply push the end of the range out to some crazy number and end up with a mile long of blanks.


What am I missing?

 

[6StringJazzer]

You should debug when the error occurs and determine what is the value of NextRow at that point. You can do this by hovering over the variable name. If you don't have any data yet, then it will start out as 1 if you have headers, and then you will be trying to set RowSource to Vendor!B2:B1 which is an invalid range. That is my best guess as to what is happening.

 

[Akuini]

This is untested:

Private Sub UserForm_Initialize()
    
UserForm1.ComboBoxVendors.List = Sheets("Vendors").Range("B2", Cells(Rows.Count, "B").End(xlUp)).Value

End Sub

 

[gemcgraw]

You should debug when the error occurs and determine what is the value of NextRow at that point. You can do this by hovering over the variable name. If you don't have any data yet, then it will start out as 1 if you have headers, and then you will be trying to set RowSource to Vendor!B2:B1 which is an invalid range. That is my best guess as to what is happening.

I have debugged NextRow and all over my other variables. Their values are correct. When I pack them in a string, they fail.

 

[gemcgraw]

This is untested:

Private Sub UserForm_Initialize()
    
UserForm1.ComboBoxVendors.List = Sheets("Vendors").Range("B2", Cells(Rows.Count, "B").End(xlUp)).Value

End Sub

That's throwing a RT 1004 "Application-Defined or Object-Defined error"

 

[Akuini]

That's throwing a RT 1004 "Application-Defined or Object-Defined error"

Oops.., forgot somethng:

Private Sub UserForm_Initialize()
With Sheets("Vendors")
UserForm1.ComboBoxVendors.List = .Range("B2", .Cells(Rows.count, "B").End(xlUp)).Value
End With
End Sub

 

[gemcgraw]

Oops.., forgot somethng:

Private Sub UserForm_Initialize()
With Sheets("Vendors")
UserForm1.ComboBoxVendors.List = .Range("B2", .Cells(Rows.count, "B").End(xlUp)).Value
End With
End Sub

Buddy that worked!! You get a big gold star! I have NEVER had that kind of trouble loading a range of values into a list or combobox before. I have been working on this for two months. I'll try to compare yours and see what exactly I was doing wrong. Thanks a million!

 

[Akuini]

Buddy that worked!! You get a big gold star! I have NEVER had that kind of trouble loading a range of values into a list or combobox before. I have been working on this for two months. I'll try to compare yours and see what exactly I was doing wrong. Thanks a million!

You're welcome & thanks for replying

 

[6StringJazzer]

I didn't pick up the first time. Your problem is a missing qualification of Rows.

Dim Source As String
    'Find the last populated row in the range.    
        NextRow = Sheets("Vendors").Range("B" & [COLOR=#ff0000]Sheets("Vendors").[/COLOR]Rows.Count).End(xlUp).Row

When you reference Rows, you must qualify it with the appropriate worksheet, or it will default to the active worksheet. This is a very common error inside a qualified Range expression.

Akuini fixed that in his solution, although you could also fix it in your original code.