-
Sep 4th, 2007, 01:20 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] [Excel] How do you Load data into a listbox?
I have a list box that I want to load with more than one column of data.
I have this, which only populates the list box with one column of data. How do I adjust it to add an entire range. I tried to change the range to A2:M2 but that didnt work. I tried to look in help for an explaination of the Offset property, but help isnt working for me, it returns not found.
Private Sub UserForm_Initialize()
Range("A2").Select
Do Until ActiveCell.Value = ""
lstPlanItems.AddItem CStr(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select
End Sub
Thank you.
Swoozie
Somedays you just should not get out of bed.
-
Sep 4th, 2007, 01:30 PM
#2
Re: [Excel] How do you Load data into a listbox?
Hi Swoozie
You can achieve that by setting the ColumCount Property to 13 (Col A to Col M). You can do it at design time or you can do it in runtime, whichever is convenient to you
ListBox1.ColumnCount = 13
Hope this helps...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 4th, 2007, 02:18 PM
#3
Thread Starter
Hyperactive Member
Re: [Excel] How do you Load data into a listbox?
I did set the column count to 13, and I sized the columns. I did this in the properties of the list box. But I still only see the 1st column
Swoozie
Somedays you just should not get out of bed.
-
Sep 4th, 2007, 02:29 PM
#4
Re: [Excel] How do you Load data into a listbox?
Check for these....
1) Did you set the range?. I mean did you set the ListFillRange property as Sheet1!A2:M2? (note: replace sheet1 with the name of your sheet and A2:M2 with the relevant range)
2) Did you resize the listbox?
3) Do you see a scroll bar?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 4th, 2007, 02:38 PM
#5
Thread Starter
Hyperactive Member
Re: [Excel] How do you Load data into a listbox?
The code I had is pasted above, but here it is again. I tried to change the range to A2:M2" but that didnt work.
Code:
Private Sub UserForm_Initialize()
Range("A2").Select
Do Until ActiveCell.Value = ""
lstPlanItems.AddItem CStr(ActiveCell.Value)
ActiveCell.Offset(1, 0).Select
Loop
Range("A2").Select
End Sub
Swoozie
Somedays you just should not get out of bed.
-
Sep 4th, 2007, 02:41 PM
#6
-
Sep 4th, 2007, 03:21 PM
#7
Re: [Excel] How do you Load data into a listbox?
And if I modify your code then this will work
Code:
Private Sub CommandButton2_Click()
Range("A2").Select
Do Until ActiveCell.Value = ""
lstPlanItems.AddItem ActiveCell.Value
ActiveCell.Offset(0, 1).Select <= DIFFERENCE IS HERE!!!!
Loop
Range("A2").Select
End Sub
Note:
1) When you move horizontly, then it's Offset(0, 1)
2) When you move verticaly, then it's Offset(1, 0)
Hope this helps...
Last edited by Siddharth Rout; Sep 4th, 2007 at 03:26 PM.
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 5th, 2007, 07:32 AM
#8
Thread Starter
Hyperactive Member
Re: [Excel] How do you Load data into a listbox?
I do not have a ListFillRange property type in the list box properties.
Also, I want each row of the table to fill each row in the listbox so I don't think I can use really any of this code that I started with, because I want to use a range not a cell.
But the really scary part is that i can update the text boxes with the values I can not see in the tables. This code works.
Code:
Me.txtStepNum.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 0).Value)
Me.cboProjectCategory.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 1).Value)
Swoozie
Somedays you just should not get out of bed.
-
Sep 6th, 2007, 03:59 AM
#9
Re: [Excel] How do you Load data into a listbox?
Originally Posted by swoozie
I do not have a ListFillRange property type in the list box properties.
THIS I NEED TO SEE MYSELF
Can you please upload your file???
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 6th, 2007, 07:11 AM
#10
Thread Starter
Hyperactive Member
Re: [Excel] How do you Load data into a listbox?
No, I can not upload , but the last code item I used using the rowsource was
Code:
Private Sub UserForm_Initialize()
'populate all listboxes
Dim c As Control
'this is just code I got from a tutorial, it really is unneeded in ths application
'but good for future reference
For Each c In Me.Controls
'format the listboxes
If TypeOf c Is MSForms.ListBox Then
c.ColumnCount = 13
c.ColumnWidths = "50;50;50;50;50;50;50;50;50;50;50;50;50"
End If
Next c
'I have no idea if this is required, but It seems that it should be
Worksheets("Plan").Activate
'Insert Code here to find the end of the range
'Populate the listbox via the rowsource
With Me.lstPlanItems
.RowSource = "A2:M244"
End With
End Sub
Populate the corresponding text boxes
Code:
Me.txtStepNum.Text = CStr(ActiveCell.Offset(lstPlanItems.ListIndex, 0).Value)
Thank you
Swoozie
Somedays you just should not get out of bed.
-
Sep 8th, 2007, 12:47 PM
#11
Re: [Excel] How do you Load data into a listbox?
Oh, Ok
Before I paste the code lemme confirm my understanding...
1) The Listbox is in a userform in Excel 2003
2) you want to populate data from say three columns into the listbox which is in say sheet1 and
3) You want to do this via code for all listboxes in that form...
If yes, then this should help...
Code:
Private Sub CommandButton1_Click()
Dim c As Control
For Each c In Me.Controls
'format the listboxes
If TypeOf c Is MSForms.ListBox Then
'IF you want data from say three columns (A,B and C)
c.ColumnCount = 3
c.RowSource = "Sheet1!A1:C1"
c.Width = 577.5
End If
Next c
End Sub
Hope this helps...
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Sep 10th, 2007, 09:07 AM
#12
Thread Starter
Hyperactive Member
Re: [Excel] How do you Load data into a listbox?
Close, I guess i am just not quite clear enough.
I want to populate 1 listbox that has 13 columns (if possible)
Then I want the user to be able to click on the item in the list box to populate textboxes for editing.
for some reason, my code doesnt work. If I populate only the first column to the list box, then I can populate the text boxes, If i load the listboc, then I can't populate the textboxes.
What I really would like is something like access uses where you query the data, update the list box, from the query. So if the user only wants to see items that are black, they can select that.
Swoozie
Somedays you just should not get out of bed.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|