VBA for Userform to add data to a Table

[tigerdel]

I have a UserForm [frmAddNewCustomer] and I am trying to get the data entered into a Table [Customers] on Sheet [Customer List] in the next row in the Table when the user clicks Add

The code is:

Private Sub cmbAdd_Click()
Dim lastRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Customer List")
With ws.ListObjects("Customers")
          .ListColumns("Company Name").Range(lastRow) = txtCust.Value
          .ListColumns("Address Line 1").Range(lastRow) = txtAddress1.Value
          .ListColumns("Address Line 2").Range(lastRow) = txtAddress2.Value
          .ListColumns("Address Line 3").Range(lastRow) = txtAddress3.Value
          .ListColumns("Town").Range(lastRow) = txtTown.Value
          .ListColumns("County").Range(lastRow) = txtCounty.Value
          .ListColumns("Post Code").Range(lastRow) = txtPostCode.Value
          .ListColumns("Name").Range(lastRow) = txtCustomerName.Value
          .ListColumns("Phone").Range(lastRow) = txtPhone.Value
          .ListColumns("email address").Range(lastRow) = txtemail.Value
Unload Me
End With
End Sub

However, when I run the UserForm I don't get any error but nothing is placed in the table

Any help gratefully received

 

[Fluff]

How about like

Private Sub cmbAdd_Click()
Dim NewRow As ListRow
Dim ws As Worksheet
Set ws = Worksheets("Customer List")
With ws.ListObjects("Customers")
        With .ListRows.Add
          .Range(.Parent.ListColumns("Company Name").Index) = txtCust.Value
          .Range(.Parent.ListColumns("Address Line 1").Index) = txtAddress1.Value
'          .ListColumns("Address Line 2").Range(lastRow) = txtAddress2.Value
'          .ListColumns("Address Line 3").Range(lastRow) = txtAddress3.Value
'          .ListColumns("Town").Range(lastRow) = txtTown.Value
'          .ListColumns("County").Range(lastRow) = txtCounty.Value
'          .ListColumns("Post Code").Range(lastRow) = txtPostCode.Value
'          .ListColumns("Name").Range(lastRow) = txtCustomerName.Value
'          .ListColumns("Phone").Range(lastRow) = txtPhone.Value
'          .ListColumns("email address").Range(lastRow) = txtemail.Value
Unload Me
End With
End Sub

 

[dmt32]

Hi,
untested but try

Private Sub CmbAdd_Click()
    Dim NewRecord As ListRow
    Dim wsCustomerList As Worksheet
    Dim tblCustomers As ListObject
    
    Set wsCustomerList = ThisWorkbook.Worksheets("Customer List")
    Set tblCustomers = wsCustomerList.ListObjects("Customers")
    
    'get next table row
    Set NewRecord = tblCustomers.ListRows.Add(AlwaysInsert:=True)

    'Add Data
    With NewRecord
        .Range(1).Value = txtCust.Value
        .Range(2).Value = txtAddress1.Value
        .Range(3).Value = txtAddress2.Value
        .Range(4).Value = txtAddress3.Value
        .Range(5).Value = txtTown.Value
        .Range(6).Value = txtCounty.Value
        .Range(7).Value = txtPostCode.Value
        .Range(8).Value = txtCustomerName.Value
        .Range(9).Value = txtPhone.Value
        .Range(10).Value = txtemail.Value
    End With
    
End Sub

change the ranges where each textbox posts it data as required

Dave

 

[tigerdel]

@Fluff
Many thanks - works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.

 

[tigerdel]

Thanks @dmt32
@Fluff version works perfectly

 

[dmt32]

Thanks @dmt32
@Fluff version works perfectly

That's ok no worries - pleased found solution - appreciate the acknowledgment

Dave