CODEWITHSUNDEEP
vbaexcel
user32882
user32882

Reputation: 5877

Can listbox.rowsource property accept a range?

According to this thread: RowSource property error vba

Listbox.rowsource can accept a range.

However when I run this 

Option Explicit

Sub test()
    UserForm1.ListBox1.RowSource = ActiveSheet.Range("A1:A2")

End Sub

I get a type mismatch error. Could someone please shine some light on what is going on?

Upvotes: 0

Views: 3432

Answers (2)

Sparky In PA
Sparky In PA

Reputation: 1

I got the same error when I changed my code from having the active sheet with a named range for the RowSource to a different sheet. You can use the RefersTo property of a name as follows:

MyUserForm.Listbox1.Rowsource = ThisWorkbook.Names("rangename").RefersTo

If the named range is on the active sheet, then the following will also work:

MyUserForm.Listbox1.Rowsource = "rangename"

But who likes changing the active sheet just to load a listbox?

Upvotes: 0

paul bica
paul bica

Reputation: 10715

UserForm1.ListBox1.RowSource takes a string:

Use this: UserForm1.ListBox1.RowSource = ActiveSheet.Name & "!A1:A2"

Upvotes: 1

Related Questions