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
It works, however, I am trying to set a dynamic range like this
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
Not sure if this is the most efficient way to go about this, but when I set the case statement hard coded like this
Code:
Select Case ComboBox1.Value
Case "Sheet1"
ListBox1.RowSource = "Sheet1!A4:A200"
It works, however, I am trying to set a dynamic range like this
Code:
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