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]

I cannot replicate that error, so not sure what is going on.
Would you be able to share a sanitised version of your workbook?

 

[pujo]

I cannot replicate that error, so not sure what is going on.
Would you be able to share a sanitised version of your workbook?

Sure, how do I share the workbook?

 

[Fluff]

You would need to upload the file to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & then post the link you are given to the thread.

 

[pujo]

You would need to upload the file to a share site such as OneDrive, DropBox, GoogleDrive, mark for sharing & then post the link you are given to the thread.

Gotcha.. Let me know if you were able to get to it.
Thanks again, link below.
Sample Workbook

 

[Fluff]

I see what you mean. If you run the ShippingListNew form on it's own, everything i fine. I will have a dig later when I have more time.

 

[pujo]

I see what you mean. If you run the ShippingListNew form on it's own, everything i fine. I will have a dig later when I have more time.

Correct, I can edit and delete but not add a new address via the form.
Thanks @Fluff

 

[Fluff]

I suspect it to do with having two modal forms open at the same time, but as that's something I've never done, it will take some experimenting.

 

[Fluff]

Ok, the only way I can stop the problem is to open the userforms as modeless like frmShippingList.Show vbModal = False
and then unload the main form before showing the 2nd

Private Sub cmdNew_Click()
  Dim frm As New frmShippingListNew
    'frm.CurrentRow = ListBoxShippingAddress.ListIndex
  frm.Show vbModal = False
  Unload Me
End Sub

and then reload it when you close the new form

Private Sub cmdClose_Click()
   frmShippingList.Show
   Unload Me
End Sub

 

[pujo]

Ok, the only way I can stop the problem is to open the userforms as modeless like frmShippingList.Show vbModal = False
and then unload the main form before showing the 2nd

@Fluff
Thank you for looking into this. I will also continue to experiment with it.
I would like the form to stay open but if that is something that cannot be done, then its all good.
If you find anything else, please let me know.

Regards,
PuJo

 

[Fluff]

Glad to help & thanks for the feedback