VBA UserForm Data Entry to Table issues...

[pujo]

I have a worksheet (Sheet10) with a table (“Shipping”).

When the UserForm opens, I get the data in a UserForm listbox.

I can edit items that are already in the table, I can also delete items that are already in the table.
I cannot seem to write new items from the UserForm to the Table in a new row.
I have tried multiple ways to accomplish this and always fail. The below code is the latest attempt but fails.
Thanks in advance for any and all assistance!

Error Message: Object doesnt support this property or method
Error Line: 10 (.Cells(LastRow, 1)…..)

Private Sub ShippingFormToSheet()
  Dim LastRow As Long
  Dim rng As Range
    Set rng = Sheet10.ListObjects("Shipping").Range
    LastRow = rng.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
With Sheet10.ListObjects("Shipping") '.Range
''''''Write the data
      .Cells(LastRow, 1).Value = textboxShippingOffice.Value 'Billing Office
      .Cells(LastRow, 2).Value = textboxShippingCompany.Value 'Company
      .Cells(LastRow, 3).Value = textboxShippingAddress.Value 'Address
      .Cells(LastRow, 4).Value = textboxShippingCity.Value 'City
      .Cells(LastRow, 5).Value = comboShippingState.Value 'State
      .Cells(LastRow, 6).Value = textboxShippingZip.Value 'Zip
  End With
End Sub

 

[Fluff]

Try it like

Private Sub ShippingFormToSheet()
   Dim NewRow As Range
   Set NewRow = Sheet10.ListObjects("Shipping").ListRows.Add
   With NewRow
''''''Write the data
      .Range(, 1).Value = textboxShippingOffice.Value 'Billing Office
      .Range(, 2).Value = textboxShippingCompany.Value 'Company
      .Range(, 3).Value = textboxShippingAddress.Value 'Address
      .Range(, 4).Value = textboxShippingCity.Value 'City
      .Range(, 5).Value = comboShippingState.Value 'State
      .Range(, 6).Value = textboxShippingZip.Value 'Zip
  End With
End Sub

 

[pujo]

Try it like

Private Sub ShippingFormToSheet()
   Dim NewRow As Range
   Set NewRow = Sheet10.ListObjects("Shipping").ListRows.Add
   With NewRow
''''''Write the data
      .Range(, 1).Value = textboxShippingOffice.Value 'Billing Office
      .Range(, 2).Value = textboxShippingCompany.Value 'Company
      .Range(, 3).Value = textboxShippingAddress.Value 'Address
      .Range(, 4).Value = textboxShippingCity.Value 'City
      .Range(, 5).Value = comboShippingState.Value 'State
      .Range(, 6).Value = textboxShippingZip.Value 'Zip
  End With
End Sub

Thanks for the assistance Fluff however, when I compile, I get an error.

Error: Compile error. Argument not optional.
The code breaks on line 6, .Range(, 1).Value = textboxShippingOffice.Value 'Billing Office

 

[Fluff]

Oops, it should be

   Dim NewRow As ListRow

 

[pujo]

Oops, it should be

   Dim NewRow As ListRow

Thanks however, Method of list row failed...

 

[pujo]

@Fluff
Searching and reading different topics in the forum, I have see the comment a couple times, "convert table to range" or something of the sort, when someone had a UserForm/Table issue.
Why is it so difficult to utilize a VBA UserForm to Add, Update, Delete items in a table?

 

[Fluff]

Thanks however, Method of list row failed...

Is the sheet protected?

 

[pujo]

Is the sheet protected?

No, nothing in the workbook is protected.

 

[Fluff]

In that case this should work

Private Sub ShippingFormToSheet()
   Dim NewRow As ListRow
   Set NewRow = Sheet10.ListObjects("Shipping").ListRows.Add
   With NewRow
''''''Write the data
      .Range(, 1).Value = textboxShippingOffice.Value 'Billing Office
      .Range(, 2).Value = textboxShippingCompany.Value 'Company
      .Range(, 3).Value = textboxShippingAddress.Value 'Address
      .Range(, 4).Value = textboxShippingCity.Value 'City
      .Range(, 5).Value = comboShippingState.Value 'State
      .Range(, 6).Value = textboxShippingZip.Value 'Zip
  End With
End Sub

If it doesn't what error do you get & what line is highlighted when you click debug?

 

[pujo]

If it doesn't what error do you get & what line is highlighted when you click debug?

@Fluff
I appreciate all the help however, I am still getting an error.
Error Message: Method 'add' of object 'listrow' failed.
Excel then crashes, not enough time to click the debug button.

I even thought it was something else within the workbook so I recreated the entire book but still have the same issue.

Here is the logic, just shortened the field names a bit. Otherwise, its the same.

Private Function SaveNewAddress()
   Dim NewRow As ListRow
   Set NewRow = Sheet10.ListObjects("tblShipping").ListRows.Add
   With NewRow
      .Range(, 1).Value = txtbxShippingOffice.Value 'Billing Office
      .Range(, 2).Value = textboxCompany.Value 'Company
      .Range(, 3).Value = textboxAddress.Value 'Address
      .Range(, 4).Value = textboxCity.Value 'City
      .Range(, 5).Value = comboState.Value 'State
      .Range(, 6).Value = textboxZip.Value 'Zip
  End With
End Function