PDA

View Full Version : [SOLVED] Populating Listbox



gmaxey
03-04-2014, 08:41 AM
I received a tip (thanks snb) in a previous thread for populating a Word userform listbox using Excel data. I've adapted it slightly as shown below to see if I could use a "named" worksheet. It worked.


Private Sub UserForm_Initialize()
Dim strSource As String
strSource = ThisDocument.Path & "\Book2.xlsx"
With GetObject(strSource)
ListBox1.List = .sheets("Sheet Named Sue").Cells(1).currentregion.Value
Close False
End With
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

Couple of questions:

1. Can this method be used to exclude the first or "heading" row of an Excel column?
2. What does ".Cells(1)" do or perform in the code line? I changed that to ".Cells(2)" I seem to get the same results.
3. When I step through the code line by line using F8, my PC gives its typical soundcard notification chime after the With GetObject(strSource) line executes. However if I simply run the code, I don't get the chime, but more of a flat click sound coming from the PC internal speaker. Any reason this or harmful consequence?
4. What if I only need the data from a single column on the worksheet or data from parts of multiple columns (e.g., a range). Can currentregion be replaced with some defined range marker? Is so, how please.

Thanks.

Kenneth Hobs
03-04-2014, 09:08 AM
1. You can start there but more code is needed to trim the first row.
2. Press F1 with cursor in or near a command word to get detailed help while in the VBE. Cells(1,0) would be cell A1. CurrentRegion is why you get the same results.
3. Running the code from VBE can differ from a normal run. The sound is based on your Window's Sound settings for events such as opening an application. No harm is done.
4. Explain what you need. Most any scenario can be coded. Simple example workbooks help us help you best.

Another approach to consider is ADO. In that method, you can get content more quickly sometimes since it is working on a closed file. If you have a named region and a known sheet name, getting that data for your list is easy.

Aflatoon
03-04-2014, 09:39 AM
To exclude the header you could do something like:

With GetObject(strSource).sheets("Sheet Named Sue").Cells(1).currentregion
ListBox1.List = .Resize(.rows.Count - 1).Offset(1).Value
.Worksheet.Parent.Close False
End With

snb
03-04-2014, 12:51 PM
To exclude the headers (but including an empty 'row' at the end)


Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").Cells(1).currentregion.offset(1).Value
Close False
End With

ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

But if you use a table in the Excel file it can be done more elegantly


Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").Listobjects(1).DataBodyRange.value
Close False
End With

ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

- equivalents: Cells(1), Range("A1"), Cells(1,1)

- any range is possible as long as you load it's .values:


Private Sub UserForm_Initialize()
With GetObject(ThisDocument.Path & "\Book2.xlsx")
ListBox1.List = .sheets("Sheet Named Sue").range("C7:K20").Value
Close False
End With

ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
End Sub

gmaxey
03-04-2014, 02:13 PM
Kenneth, Aflatoon, snb;

I obviously have a lot to learn about Excel and ranges. Thanks for your comments. Kenneth, I typical use ADODB connections and it was a suggestion here to use the GetObject so I decided to investigate.

I think all of my original questions are answered so again thanks. I put together the following to help me remember what I've been taught today. Just one more question. Is the method used in ListBox6, the most efficient for returning just the entire contents of one column?



Private Sub UserForm_Initialize()
Dim strSource As String
Dim lngLastRow As Long
strSource = ThisDocument.Path & "\Book2.xlsx"
With GetObject(strSource)
ListBox1.List = .sheets("Sheet Named Sue").Cells(1).currentregion.Value
Close False
End With
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
'Exclude first row and clip empty last row.
With GetObject(strSource)
ListBox2.List = .sheets("Sheet Named Sue").Cells(1).currentregion.Offset(1).Value
Close False
End With
'Clip empty last row
ListBox2.RemoveItem (ListBox2.ListCount - 1)
ListBox2.ColumnCount = UBound(ListBox2.List, 2) + 1
'Named table
With GetObject(strSource)
ListBox3.List = .sheets("Sheet Named Sue").Listobjects("NamedTable").DataBodyRange.Value
Close False
End With
ListBox3.ColumnCount = UBound(ListBox3.List, 2) + 1
'Named range
With GetObject(strSource)
ListBox4.List = .sheets("Sheet Named Sue").Range("NamedRange").Value
Close False
End With
ListBox4.ColumnCount = UBound(ListBox4.List, 2) + 1
'Range coordinates
With GetObject(strSource)
ListBox5.List = .sheets("Sheet Named Sue").Range("A1:C4").Value
Close False
End With
ListBox5.ColumnCount = UBound(ListBox5.List, 2) + 1
'Column 1 or "A"
With GetObject(strSource)
With .sheets("Sheet Named Sue")
lngLastRow = .Cells(1).End(-4121).Row
ListBox6.List = .Range("A1:A" & lngLastRow).Value
End With
Close False
End With
ListBox6.ColumnCount = UBound(ListBox6.List, 2) + 1
End Sub

snb
03-04-2014, 02:56 PM
Frankly: no. You don't want more than a million items in a listbox.
NB. don't forget the dot before .Close

With GetObject(strSource)
ListBox6.List = .sheets("Sheet Named Sue").cells(1).currentregion.columns(1).value
.Close False
End With

or


With GetObject(strSource)
ListBox6.List = .sheets("Sheet Named Sue").columns(1).specialcells(2).Value
.Close False
End With

You'll find more on filling listboxes/comboboxes over here:

http://www.snb-vba.eu/VBA_Fill_combobox_listbox_en.html

gmaxey
03-04-2014, 03:22 PM
snb,

I'll have to go off in Excel and learn what .Specialcells(2) means, but very nice. I like the second method better because the first will leave empty rows at the end of the list box is column "A" doesn't have as many rows as say "B" or "C." That statement probably proves beyond a doubt that I'm not using Excel correctly ;-)

Thanks for the link. I'll have a look. This is my lesson for the day:


Private Sub UserForm_Initialize()
Dim strSource As String
Dim lngLastRow As Long
Dim oSheet As Object
strSource = ThisDocument.Path & "\Book2.xlsx"
With GetObject(strSource)
Set oSheet = .sheets("Sheet Named Sue")
ListBox1.List = oSheet.Cells(1).currentregion.Value
ListBox1.ColumnCount = UBound(ListBox1.List, 2) + 1
'Exclude first row and clip empty last row.
ListBox2.List = oSheet.Cells(1).currentregion.Offset(1).Value
'Clip empty last row
ListBox2.RemoveItem (ListBox2.ListCount - 1)
ListBox2.ColumnCount = UBound(ListBox2.List, 2) + 1
'Named table
ListBox3.List = oSheet.Listobjects("NamedTable").DataBodyRange.Value
ListBox3.ColumnCount = UBound(ListBox3.List, 2) + 1
'Named range
ListBox4.List = oSheet.Range("NamedRange").Value
ListBox4.ColumnCount = UBound(ListBox4.List, 2) + 1
'Range coordinates
ListBox5.List = oSheet.Range("A1:C4").Value
ListBox5.ColumnCount = UBound(ListBox5.List, 2) + 1
'Column 1 or "A" - Method 1
ListBox6.List = oSheet.Columns(1).SpecialCells(2).Value
ListBox6.ColumnCount = UBound(ListBox6.List, 2) + 1
'Column 1 or "A" - Method 2
With ListBox7
.List = oSheet.Cells(1).currentregion.Columns(1).Value
.ColumnCount = UBound(.List, 2) + 1
Do While .List(.ListCount - 1) = vbNullString
.RemoveItem (.ListCount - 1)
Loop
End With
.Close False
End With
End Sub

gmaxey
03-04-2014, 03:27 PM
snb,

The button on your webpage to get the sample file doesn't work. Seems the link is broken.

snb
03-05-2014, 01:52 AM
I'll check; thanks for reporting.

Update: you can dowload the file.