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.
Admin Edit: Also posted on Mr. Excel: https://www.mrexcel.com/board/…1-on-spreadsheet.1121121/
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
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