Setting Userform Listbox Dynamic range

[simora]

I am setting a listbox range based on the selection of a ComboBox.Value using Select case.
Not sure if this is the most efficient way to go about this, but when I set the case statement hard coded like this

        Select Case ComboBox1.Value
               Case "Sheet1"
                    ListBox1.RowSource = "Sheet1!A4:A200"

It works, however, I am trying to set a dynamic range like this

        Select Case ComboBox1.Value
              Case "Sheet2"    
                     ListBox1.RowSource = Sheet2.Range("A4", Range("A65536").End(xlUp))

and I get an error in setting the Range.

Can someone please correct my dynamic range code, and also let me know if there's a better way to populate my ListBox from the ComboBox.

Its the same Row source for every page except for the Sheet number, but COmboBox1.Value Selection sheet 1 sets the Row Source to Sheet1.Range("A4", Range("A65536").End(xlUp))
Sheet 2 follows the same pattern etc...

Code and suggestions appreciated.

Thanks

 

[GTO]

Not tested, but RowSource is looking for a string like in your first one. To dynamically assign this, you want something like:

ListBox1.RowSource = Sheet2.Name & "!" & Sheet2.Range("A4", Range("A65536").End(xlUp)).Address

Mark

 

[Smitty]

Perhaps:

ListBox1.RowSource = Sheets("Sheet2").Range([A4], Cells(Rows.Count,"A").End(xlUp))

HTH,

 

[simora]

Thanks GTO:

This is the error that I get with your code.

Object Worksheet failed

 

[simora]

Smitty:

Your code generated this error:

Application Defined OR Object Defined Error

Seems like it should've worked.

 

[mikerickson]

ListBox1.RowSource = Range(Sheet1.Range("A4"), Sheet1.Range("A65536").End(xlUp)).Address(,,,True)

 

[GTO]

Thanks GTO:

This is the error that I get with your code.

Object Worksheet failed

Sorry - I plopped that in there a bit quick and hadn't caught the lack of qualifying.

    ListBox1.RowSource = Sheet2.Name & "!" & Sheet2.Range("A4", Range("A65536").End(xlUp)).Address

My first one would run, but only if Sheet2 is active (not my intent, that is the part I missed)

As you can see with Mike's at #6, he catches this. Here is like Mike did, but I think should self-adjust for 2007.

    With Sheet2
        ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)
    End With

@Mike:

Cool! I sorta knew it was there, but haven't really used it (external ref). Thanks

Mark

 

[simora]

Hi Thanks:

Both code posted by mikerickson & GTO, Mike & Mark worked perfectly.

Again Thanks!