RowSource Question

[JohnSearcy]

Hi -

Just a simple question......
I want to set the RowSource for the ComboBox on the UserForm in the actual Properties window in the VBA editor and I cannot find the proper way. Always getting the Invalid Property value message. I know how to do it through VBA and it would be simple and work but this is ticking me off. I have tried numerous ways that I have found on the web and nothing works. A recent example of one of the MANY ways I tried:

"=Sheet8!$A$1:$A$104"

I had the sheet named LINING STOCK # but changed it to try it as Sheet8. Still wouldn't work. I would like the sheet to be named LINING STOCK #. It has to be something simple that I have not tried or I may be trying to do something that there is no solution for. Using Office 365 at work. Suggestions?

Thank you,
John

 

[6StringJazzer]

The example you showed will work if you omit the "=" sign. I assume you are not really putting the quotes in there either.

Sheet8!$A$1:$A$104

After renaming you can use

'LINING STOCK #'!$A$1:$A$104

 

[JohnSearcy]

Thank you 6StringJazzer! Out of all the ones I tried, somehow those didn't make it in there. Thank you, again!

 

[My Aswer Is This]

Thank you 6StringJazzer! Out of all the ones I tried, somehow those didn't make it in there. Thank you, again!

Just curious. Why do you want to use rowsource and not Vba

 

[JohnSearcy]

For no particular reason, I guess other than to do it differently this time around to keep fresh and keep learning and try not to forget stuff...... if that all made sense.

 

[My Aswer Is This]

Here is an example of how I load A Combobox

A script like this can run when you open The Userform.

Now this example is used when you click a command button
But this script could be made to run when you open the UseForm

Private Sub CommandButton1_Click()
'Modified  6/13/2022  11:02:10 PM  EDT
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row

With ComboBox1
    .Clear
    .List = Range("A1:A" & Lastrow).Value
End With
End Sub

 

[JohnSearcy]

Thank you for that! Quite helpful! I could use that example to make better or shorten some script I have or will write in the future.

 

[6StringJazzer]

Just curious. Why do you want to use rowsource and not Vba

You didn't ask me, but I try to push as much work into the worksheet as I can--I let Excel do my coding for me whenever possible. If it's a static range, I hardcode the range into RowSource. If it's a dynamic range, I create a dynamic named range and use that in RowSource. I'm not arguing that it's "better" but that is "why".