diksuchi
asked on
ControlSource Property in Listbox
Hi,
Im trying to assign a series of records to a listbox from a Range on an excel sheet. the range has the column headers on the row immediately above it (Row(0)). If I assign this to a listbox control source it errors out saying Type Mismatch (in some cases) and "Cannot set controlsource property" etc. I'm trying to get the headers to be displayed as well.
Anyone help?
Thanks.
Here's a code sample:
With [Trades]
lstTrades.ColumnCount = .Columns.Count
lstTrades.ColumnHeads = True
lstTrades.ControlSource = Range(.Cells(0, 1), .Cells(.Rows.Count,.Column
End With
Im trying to assign a series of records to a listbox from a Range on an excel sheet. the range has the column headers on the row immediately above it (Row(0)). If I assign this to a listbox control source it errors out saying Type Mismatch (in some cases) and "Cannot set controlsource property" etc. I'm trying to get the headers to be displayed as well.
Anyone help?
Thanks.
Here's a code sample:
With [Trades]
lstTrades.ColumnCount = .Columns.Count
lstTrades.ColumnHeads = True
lstTrades.ControlSource = Range(.Cells(0, 1), .Cells(.Rows.Count,.Column
End With
ASKER
I just tried that and it says "Permission Denied"?!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nope. "Permission Denied" :-(
Is that your whole code? You're not trying to use AddItem somewhere in there too, are you?
ASKER
hi there,
Extremely sorry, with this not working I had added a lstTrades.List = [Trade.ListData].Value
which was giving the Permission Denied error.
But other than that, its back to square one. It doesn't show the values - it actually looks like its populating the listbox (as I can select 2 rows) and see the column separators for the header - but I can no data is actually visible - either the headers or the actual data itself.
Extremely sorry, with this not working I had added a lstTrades.List = [Trade.ListData].Value
which was giving the Permission Denied error.
But other than that, its back to square one. It doesn't show the values - it actually looks like its populating the listbox (as I can select 2 rows) and see the column separators for the header - but I can no data is actually visible - either the headers or the actual data itself.
are you sure the Trade range contains data? Can you post a workbook showing the problem?
You can post it at www.ee-stuff.com. In order to upload to ee-stuff you need to do the following:
1. Zip your file.
2. Login with the same details as here
3. Switch to the Expert tab, and choose to upload a new file.
4. Enter the question number - 22956871 in this case - browse to your *zipped* file, then you must enter a comment of some sort (it doesn't matter what) and press Upload.
5. You should then be taken to a page with two links to the file on it - copy the second one and post it back here. **If you do not see the links, then your file did NOT upload.**
Regards,
Rory
You can post it at www.ee-stuff.com. In order to upload to ee-stuff you need to do the following:
1. Zip your file.
2. Login with the same details as here
3. Switch to the Expert tab, and choose to upload a new file.
4. Enter the question number - 22956871 in this case - browse to your *zipped* file, then you must enter a comment of some sort (it doesn't matter what) and press Upload.
5. You should then be taken to a page with two links to the file on it - copy the second one and post it back here. **If you do not see the links, then your file did NOT upload.**
Regards,
Rory
ASKER
Got it! Whew! Was quite weird, I moved the relevant form and sheet alone to an external wkbook and it worked over there while commenting out everything else and running it still wouldn't work here. The problem was it needed a sheet reference as well - instead of [Trades].address it had to be [Trade].Parent.name & "!" & [Trade].Address (though I dont have any other range of the same name)
Thanks a lot. Am going to accept the previous post as solution!
Thanks a lot. Am going to accept the previous post as solution!
Regards,
Rory