Run time error 438 when trying to use userform into listobject

[KarEngVzla]

Hello All, New user of Excel 2013 here.

I have been trying my best to work out a userform which would feed information into a table. This is a simple example Im creating in order to apply it to a much more complicated model which I'll have to design.

I named the table "NamesTable" and it would have three columns: Name, Charge and Food. Then I designed a userform (NameChargeFood) which when prompted would ask you to input the Name of a person, their position in a company and finally their favorite food. I intend on adding error handling and other stuff at the end, but first i have to figure out how to make it work.

Ideally the macro would add a new row to the bottom of the table and then fill the cells with the information that was added to the textboxes within the userform. However instead all it does is return the dreaded Run-Time error '438' and tell me that the object doesn't support this property or method.

Private Sub CancelButton_Click()


Unload Me


End Sub


Sub Addrow()


ActiveSheet.ListObjects("NamesTable").ListRows.Add


End Sub


Private Sub UserForm_Click()


TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""


End Sub




Private Sub CommandButton1_Click()


Dim oSh As Worksheet
Dim NamesTable As ListObjects
Dim NewRow As ListRow


Set oSh = ActiveSheet
Set NamesTable = ActiveSheet.ListObject(1)
Set NewRow = NamesTable.ListRow.Add




NewRow.Range(1, 1).Value = TextBox1.Value
NewRow.Range(1, 2).Value = TextBox2.Value
NewRow.Range(1, 3).Value = TextBox3.Value




End Sub

The line that excel says has the error is the:

"Set NamesTable = ActiveSheet.ListObject(1)"

I've tried, for about 2 weeks, to alter the code in order to make it work, however nothing seems to make it budge. I'm quiet new at this and I feel like Im missing something.

 

[Domenic]

Try...

Sub AddRow()

    Dim oNewRow As ListRow
    
    Set oNewRow = ActiveSheet.ListObjects("NamesTable").ListRows.Add
    
    oNewRow.Range.Cells(1, 1).Value = TextBox1.Value
    oNewRow.Range.Cells(1, 2).Value = TextBox2.Value
    oNewRow.Range.Cells(1, 3).Value = TextBox3.Value
    
    Set oNewRow = Nothing
    
End Sub

Hope this helps!

 

[KarEngVzla]

Thank you so much!

it works perfectly now. Now I just have to modify it to work in the one I said before but shouldn't be too hard.

Domenic, I owe you a hug and a Beer. I can't thank you enough!

 

[Domenic]

You're very welcome! Glad I could help!

Cheers!