Populating Listbox

[gmaxey]

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]

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]

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]

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]

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]

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]

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]

snb,

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

[snb]

I'll check; thanks for reporting.

Update: you can dowload the file.