|
fill a listbox with data
Hi All,
I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
I ran into this problem a few weeks ago.
To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
Neily,
thnx! for your effort.. The listbox is only a view tool for the user, therefore there is no problem that it is static.. (Am I using the wrong tool here?) the code does works fine, because I would also like to use the columnheads.. However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr 380) any idea? -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Neily" wrote: I ran into this problem a few weeks ago. To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
Using RowSource is the only way to get column heads.
ListBox4.RowSource = "Sheet1!A2:D8" should work fine What is the text of error 380? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Arjan" wrote in message ... Neily, thnx! for your effort.. The listbox is only a view tool for the user, therefore there is no problem that it is static.. (Am I using the wrong tool here?) the code does works fine, because I would also like to use the columnheads.. However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr 380) any idea? -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Neily" wrote: I ran into this problem a few weeks ago. To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
Bob,
I used a space in my sheetname. Is this not allowed? Because, when I used an other sheetname, without a space it works fine! However, problem solved.. thnx!! -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Bob Phillips" wrote: Using RowSource is the only way to get column heads. ListBox4.RowSource = "Sheet1!A2:D8" should work fine What is the text of error 380? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Arjan" wrote in message ... Neily, thnx! for your effort.. The listbox is only a view tool for the user, therefore there is no problem that it is static.. (Am I using the wrong tool here?) the code does works fine, because I would also like to use the columnheads.. However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr 380) any idea? -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Neily" wrote: I ran into this problem a few weeks ago. To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
I see, in that case use something like
ListBox1.RowSource = "'Sheet 2'!A2:D8" note the single quotes around the sheet name as well as double quotes around the whole. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Arjan" wrote in message ... Bob, I used a space in my sheetname. Is this not allowed? Because, when I used an other sheetname, without a space it works fine! However, problem solved.. thnx!! -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Bob Phillips" wrote: Using RowSource is the only way to get column heads. ListBox4.RowSource = "Sheet1!A2:D8" should work fine What is the text of error 380? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Arjan" wrote in message ... Neily, thnx! for your effort.. The listbox is only a view tool for the user, therefore there is no problem that it is static.. (Am I using the wrong tool here?) the code does works fine, because I would also like to use the columnheads.. However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr 380) any idea? -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Neily" wrote: I ran into this problem a few weeks ago. To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** |
fill a listbox with data
I like to let excel worry about the syntax.
I'd use: ListBox4.RowSource _ = worksheets("Sheet1").range("A2:D8").address(extern al:=true) or ListBox4.RowSource _ = worksheets("Sheet 2").range("A2:D8").address(external:=true) Arjan wrote: Bob, I used a space in my sheetname. Is this not allowed? Because, when I used an other sheetname, without a space it works fine! However, problem solved.. thnx!! -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Bob Phillips" wrote: Using RowSource is the only way to get column heads. ListBox4.RowSource = "Sheet1!A2:D8" should work fine What is the text of error 380? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Arjan" wrote in message ... Neily, thnx! for your effort.. The listbox is only a view tool for the user, therefore there is no problem that it is static.. (Am I using the wrong tool here?) the code does works fine, because I would also like to use the columnheads.. However, when I use ListBox4.RowSource = "Sheet1!A2:D8" i get an error (nr 380) any idea? -- Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** "Neily" wrote: I ran into this problem a few weeks ago. To be able to add items to a listbox, you need to use the additem method.... listbox1.additem "item1" but because you've specified the rowsource of the listbox, this makes it static and thus you can't add items to it. What you need to do is set up a loop to add your items to the list box via the additem method and then use the additem method in a button click event. eg. This will add everything from A2 to A8 to a listbox For MyLoop=2 to 8 ListBox1.AddItem Sheets("Sheet1").Range("A" & MyLoop).Value Next MyLoop I'm not sure off hand how to do the columns, but the help files should be able to tell you that. Oh and if you want to specify a sheet name in the rowsource property, I think it goes something like... ListBox4.RowSource = "Sheet1!A2:D8" using a ! to separate the sheetname from the range. "Arjan" wrote: Hi All, I would like to fill a list box with a click on a commandbutton. The code I use is printed below. ---------------------------- Private Sub CommandButton3_Click() ' De listboxen vullen met de juiste data ListBox1.RowSource = "A2:D8" ListBox1.ColumnCount = 4 ListBox1.ColumnWidths = "360;25;50;50" ListBox2.RowSource = "A2:D10" ListBox2.ColumnCount = 4 ListBox2.ColumnWidths = "360;25;50;50" ListBox3.RowSource = "A2:D14" ListBox3.ColumnCount = 4 ListBox3.ColumnWidths = "360;25;50;50" ListBox4.RowSource = "A2:D18" ListBox4.ColumnCount = 4 ListBox4.ColumnWidths = "360;25;50;50" End Sub ---------------------------- However, I would like to use more than one sheet.. and the data selected with this code is only the data from sheet1. How can I specify the sheet where the data has to be collected from? thank you all.. :) Arjan Bregman ***** the knowledge is always there, maybe hidden, but it is there.. ***** -- Dave Peterson |
| All times are GMT +1. The time now is 01:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com