ListFillRange

[WelshyWizard]

I'm new to this so please bare with me...

In Excel I'm trying to fill a listbox on sheet1 with a range of data on sheet2.
I try to do this with the dode below:

Sheet1.ListBox1.ControlFormat.ListFillRange = "Sheet2!A5:B11"

However, when I run this I get the following error:
'Object Required'

I'm a bit confused by this... I've set the column count of the listbox to 2 and I've also managed to link to the selected field through the list boxes properties.... What am I doing wrong?!

If I get this working I'm planning upon changing the range for a variable range that will be different everytime, but 1st things 1st!

Thanks.

 

[PHV]

Perhaps this ?
ActiveWorkbook.Sheets("Sheet1").ListBox1.ControlFormat.ListFillRange = "Sheet2!A5:B11"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Hmm, probably hit submit too fast.
I guess you have to play with the Shapes collection ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[WelshyWizard]

Thanks PH,

Unfortunatley I now get:
'Object doesn't support this property or method'

Any other ideas? I've tried loads of variations and looked on loads of sites but just can't get it to work!

Today is the tomorrow you worried about yesterday - and all is well.....

 

[vladk]

ComboBox1.ListFillRange = Sheets("Sheet2").Range("A1:A4").AddressLocal(External:=True)

 

[firefytr]

Sheet1 is the code name. That error message means Excel cannot find the object you specified (with a code name of ListBox1) within that sheet. Ensure you are using the correct name on the correct sheet.

-----------
Regards,
Zack Barresse

 

[WelshyWizard]

Ok..... I can get this code to work... However, I can't seem to get into the properties to change the columncount to 2 so it is only showing the 1st column.... Any ideas how to do this using code? I know there is a columncount command but this only seems to be for forms?

Cheers.

Today is the tomorrow you worried about yesterday - and all is well.....

 

[WelshyWizard]

Sorry! I forgot to actually add the code into my post!

With Worksheets(1)

        Set lb = .Shapes.AddFormControl(xlListBox, 10, 10, 200, 100) 
        lb.ControlFormat.ListFillRange = "Sheet2!A5:B11"
      
End With

Today is the tomorrow you worried about yesterday - and all is well.....

 

[WelshyWizard]

I used the .additems command in the end... Worked a treat!

Thanks for all of your help.

Today is the tomorrow you worried about yesterday - and all is well.....