VBA Userform Populate a Listbox from Table

[sxhall]

Hi,

So I am missing the complete obvious here but I am trying to populate a listbox in a userform from an named excel table. I have managed to do this by creating a named range that refers to the table, code A below. But when I am using my userform to delete entries it is crashing out and restarting Excel!

Code A - Populate the userform on initialize

Private Sub UserForm_Initialize()

ListBox1.ColumnCount = 2
ListBox1.RowSource = "ListBoxDayCodes"
TextBoxSC.SetFocus
Label5.Caption = ""

End Sub

Code B is the code I am using to delete an entry from the table via the userform

Code B - Delete an entry from the table. RemVal is the entry the user has selected from the listbox in the userform to find and delete the entry from the table.

Private Sub CBRemove_Click()

Dim rng As Range
Dim LRow As Long
Dim RemVal As String

If ListBox1.ListIndex = -1 Then 
Else
    Set rng = Sheet17.ListObjects("TDayCodes").Range
    
    RemVal = ListBox1.Column(0)
    
    LRow = rng.Find(What:=RemVal, _
    After:=rng.Cells(1), _
    Lookat:=xlPart, _
    LookIn:=xlFormulas, _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious, _
    MatchCase:=False).Row
    
    With Sheet17
        .ListObjects("TDayCodes").ListRows(LRow - 5).Delete
    End With
    
    ListBox1.ColumnCount = 2
    ListBox1.RowSource = "ListBoxDayCodes"
    TextBoxSC.SetFocus

End If
End Sub

So in the example below 'Short Code' "NYD" would be searched for in table 'TDayCode' and the table row deleted.

I think that the error is occurring as I have the table set up as a named range in name manager ("ListBoxDayCodes" in the above code). So what I want to be able to do is to populate a two column listbox directly from the table 'TDayCode' instead to get around having it as a named range.

I have searched and tried several things but cannot find the secret sequence to get it to work for me.

If any one can assist in populating a userform listbox directly from a table it would be greatly appreciated.

Thanks in advance

Steven

 

[Fluff]

Don't use RowSource instead load the table like

    ListBox1.List = Sheet17.ListObjects("TDayCodes").DataBodyRange.Value2

 

[sxhall]

Don't use RowSource instead load the table like

    ListBox1.List = Sheet17.ListObjects("TDayCodes").DataBodyRange.Value2

Thanks Fluff,

But I now get "permission denied error 70" with my code for initializing as follows...

Private Sub UserForm_Initialize()

ListBox1.List = Sheet17.ListObjects("TDayCodes").DataBodyRange.Value2
TextBoxSC.SetFocus
Label5.Caption = ""

End Sub

I love Excel but sometimes....?!!!

Steven

 

[Fluff]

Check that you don't have anything in the listbox properties for rowsource.

 

[sxhall]

Check that you don't have anything in the listbox properties for rowsource.

Massive face palm there from me, forgot I had populated the rowsource.

I have amended my code with this for initialize, for adding new code and deleting a code and no issues whatsoever now,

Many thanks. I'd spent for to long trying to sort this but to avail.

Cheers

Steven

 

[Fluff]

Glad to help & thanks for the feedback.