using userform to populate Table1 on spreadsheet

[JimUSMC]

Hello everyone,



I have been researching and researching with building userforms and their reactions with the spreadsheets. I came across so many different ways to accomplish the same thing. It's crazy how many people do this differently. So I came out with this fully functioning coding that does the trick.... except when I converted my spreadsheet list to a table (Table1), all hell broke loose.. haha. nothing works. Can anyone help me with altering the code WITHOUT completely rewriting the entire script? And for those who are seeking the same assistance, I will share my current coding and if I can get help with this, I will share the end result as well.

USERFORM:

Private Sub cmdResetItem_Click()
 Dim msgValue As VbMsgBoxResult
 
 msgValue = MsgBox("Do you want to reset the form?", vbYesNo + vbInformation, "Confirmation")
 
 If msgValue = vbNo Then Exit Sub
 
 Call ResetItem
 
End Sub
Private Sub cmdSaveItem_Click()
 
 Dim msgValue As VbMsgBoxResult
 
 msgValue = MsgBox("Do you want to save the data?", vbYesNo + vbInformation, "Confirmation")
 
 If msgValue = vbNo Then Exit Sub
 
 Call SubmitItem
 Call ResetItem
 
End Sub
Private Sub UserForm_Initialize()
 Call ResetItem
End Sub

MODULE:

    Option Explicit
    Sub ResetItem() ' The reset button coding
     Dim iRow As Long
    
     iRow = [Counta(ItemDatabase! A:A)] 'identify last row
    
     With ItemForm
     .txtItemCode.Value = ""
     .txtItemName.Value = ""
     .txtItemDescription.Value = ""
     .txtSupplierName.Value = ""
    
     .lstItemDatabase.ColumnCount = 5
     .lstItemDatabase.ColumnHeads = True
     .lstItemDatabase.ColumnWidths = "30,60,150,400,45"
    
     If iRow > 1 Then
     .lstItemDatabase.RowSource = "ItemDatabase! A2:E" & iRow
     Else
     .lstItemDatabase.RowSource = "ItemDatabase! A2:E2"
     End If
    
     End With
    End Sub
    Sub SubmitItem()
     Dim sh As Worksheet
     Dim iRow As Long
    
     Set sh = ThisWorkbook.Sheets("ItemDatabase")
    
     iRow = [Counta(ItemDatabase! A:A)] + 1
    
     With sh
    
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
    
     End With
    
    End Sub
    Sub Show_Form()
     ItemForm.Show
    End Sub

 

[Yongle]

Instead of these lines

   Set sh = ThisWorkbook.Sheets("ItemDatabase")
     iRow = [Counta(ItemDatabase! A:A)] + 1
     With sh
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
     End With

Try

    With Sheets("ItemDatabase").ListObjects(1).ListRows.Add
        .Range(1) = .Range.Row - 1
        .Range(2) = ItemForm.txtItemCode.Value
        .Range(3) = ItemForm.txtItemName.Value
        .Range(4) = ItemForm.txtItemDescription.Value
        .Range(5) = ItemForm.txtSupplierName.Value
    End With

 

[JimUSMC]

Instead of these lines

   Set sh = ThisWorkbook.Sheets("ItemDatabase")
     iRow = [Counta(ItemDatabase! A:A)] + 1
     With sh
     .Cells(iRow, 1) = iRow - 1
     .Cells(iRow, 2) = ItemForm.txtItemCode.Value
     .Cells(iRow, 3) = ItemForm.txtItemName.Value
     .Cells(iRow, 4) = ItemForm.txtItemDescription.Value
     .Cells(iRow, 5) = ItemForm.txtSupplierName.Value
     End With

Try

    With Sheets("ItemDatabase").ListObjects(1).ListRows.Add
        .Range(1) = .Range.Row - 1
        .Range(2) = ItemForm.txtItemCode.Value
        .Range(3) = ItemForm.txtItemName.Value
        .Range(4) = ItemForm.txtItemDescription.Value
        .Range(5) = ItemForm.txtSupplierName.Value
    End With

Yongle, thanks for the effort. made my excel crash. the line (With Sheets("ItemDatabase").ListObjects(1).ListRows.Add) highlighted with yellow telling me it disconnected with client.. not sure what happened.

 

[Yongle]

Neither do I
The code is standard stuff (using sheet name provided in your code and referring to the first table on that sheet)
I have seen tables corrupted inadvertently due to user actions
- it is safer when users are prevented from making ANY alterations to a table EXCEPT via userform

except when I converted my spreadsheet list to a table (Table1), all hell broke loose.

Perhaps a minor corruption occurred when "all hell broke loose" ??

Is your table an Excel table created with Insert \ Table
- or is there something else going on ?

I suggest you create a NEW test file with a NEW table and NEW userform to test the code
- it works without any issues for me and I am also on 365

 

[Fluff]

Crossposted using excel userform to populate table1 - OzGrid Free Excel/VBA Help Forum

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.