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