2

I have a list of names in Column A in a worksheet named "Email"

I want to populate a userform ListBox with the names Column A. However, I can't specify a fixed range as this list will grown and shrink. So how do I get the userform to populate the list with the correct number of items?

This is what I am currently trying but is not working (I'm sure it will be obvious to some people on here as to why not), I also saw another example using a simple For loop but I am unable to find the example again to show you.

Private Sub UserForm_Initialize()

Dim rngName As Range
Dim rng1 As Range
Dim rng2 As Range
Dim ws As Worksheet


Set ws = Worksheets("Email")
Set rngName = ws.Range("A:A").Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
Set rng1 = ws.Range("A1")

On Error GoTo ErrorHandle

Me.lbUsed.List = Range(rng1 & ":" & rngName).Value

ErrorHandle:

End Sub

EDIT:

I now have the following code but it is failing to work when I load the userform:

Private Sub UserForm_Initialize()

Dim rngName As Range
Dim rng1 As Range

Set rngName = Worksheets("Email").Range("A:A").Cells.Find(What:="*", LookAt:=xlWhole, MatchCase:=False, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)

Set rng1 = Worksheets("Email").Range("A1:" & rngName.Address)

Me.lbUsed.List = Worksheets("Email").Range(rng1).Value


End Sub

Can anyone point me in the correct direction?

Petay87
  • 1,700
  • 5
  • 24
  • 39

3 Answers3

5

If you want to populate your listbox with all of the items in column A (assuming that these are in a continuous range), you could do this simply by modifying you code like this:

Private Sub UserForm_Initialize()
    Dim rngName As Range
    Dim ws As Worksheet
    Dim i As Integer

    Set ws = Worksheets("Email")
    For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
        If ws.Cells(i, 1).Value <> vbNullString Then Me.lbUsed.AddItem ws.Cells(i, 1).Value
    Next i
End Sub
Netloh
  • 4,338
  • 4
  • 25
  • 38
  • using `.End(xlDown)` is very unreliable. What would be if `A1` is empty? this may be interesting: http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba – Dmitry Pavliv Feb 24 '14 at 20:07
  • That did the trick perfectly! Can you explain this part please?: Set rngName = ws.Range("A1", ws.Range("A1").End(xlDown)) – Petay87 Feb 24 '14 at 20:08
  • @simoco That is true, but that is why I added the part about it being a `continuous range`. – Netloh Feb 24 '14 at 20:08
  • It would always be a continuous range with no gaps so that works fine in this example. – Petay87 Feb 24 '14 at 20:10
  • 1
    @Petay87 It works like the keyboard shortcut ctrl+shift+down when you are standing in cell `A1`. So it states the range from A1 and downward. But as @simoco correctly points out, this only works as expected on a continuos range. – Netloh Feb 24 '14 at 20:13
  • Ok so this no longer works as there have been items removed from the range and it is now populating blank items. – Petay87 Feb 25 '14 at 12:14
  • 1
    @Petay87 I have updated the code so that it will only add items from non-blank cells. – Netloh Feb 25 '14 at 12:41
  • @SørenHoltenHansen That now works exactly as I want! – Petay87 Feb 25 '14 at 12:44
3

Point the RowSource property of the ListBox to dynamic Named Range in your spreadsheet. As you add or remove items to the range, the list will automatically pull in new items to the listbox. There's no need to write any code implement this requirement.

Joe-in-VT
  • 29
  • 3
0

This is an old question, but I feel this second comment is good. I would like to add a little to it to help people achieve the described answer:

  1. Create (ctrl + t) a table and open Formulas>Name Manager
  2. Select doublec-click the table in the list of names and name it enter image description here
  3. In your userform, enter the name of the table (that you named it in 2.) in RowSource: enter image description here

When you open the form you should see all the items in the table listed in the listbox: enter image description here

If you add to the table you add to that listbox's list, Here's a little code example to remove an item and reset the table size to fit:

Sub ChangeTableSize()
Dim n As Long
Dim Tbl As ListObject
 Set Tbl = Sheets(8).ListObjects(9)
  With Tbl
   n = .DataBodyRange.Rows.Count
       .DataBodyRange(20, 1).ClearContents
       .Resize Range(Tbl.DataBodyRange.Resize(n, 1).Offset(-1, 0).Address)
  End With
End Sub

and Here's one to add to the table:

Sub AddtoTable()
Dim n As Long
Dim Tbl As ListObject
 Set Tbl = Sheets(8).ListObjects(9)
 With Tbl
     n = .DataBodyRange.Rows.Count
    Tbl.DataBodyRange(n + 1, 1) = "New"
 End With
End Sub

Hope it is found to be useful!

Reverus
  • 133
  • 9