Code to transfer data entered in userform to table

[Channce]

Hello all...I used to think I was great with excel until I started making VBA userforms. I've created the form and the button to show the form (which does work), but I can't seem to figure out the code to transfer the data from each field to the respective row in the table. It shows I don't have permission to attach screenshots, so I attached a link to them here...

screenshot of table in excel --- https://drive.google.com/open?id=1NqL1KN7iXpkZTq6w-9uwF3YmpVbrDjKP
screenshot of userform --- https://drive.google.com/open?id=1qp8ip1ok9ObOnxQZcGth9LO-QgNCI8bK

What I need specificially...
- All fields EXCEPT the checkbox need to have a value entered or it should present a msgbox saying "You must enter something in every field before saving."
- When you press save item, I need the data to transfer to the next available row and fill into the respective fields. (I know I haven't provided the exact names of each text box but I can fill them in).
- After entry has been saved, I would like the form to close.

I will also be creating another form to pull up an existing entry that will pull up the existing info on that row about the item, and have the ability to change that and save which will over write that data on that row... I haven't started on that yet, because I wanted to try this first.

Any help would be much appreciated!!
-Chance

 

[mumps]

It would make it easier to test possible solutions if you could attach a link to your file rather than a screen shot.

 

[Channce]

Mumps, You're right... Sorry about that. Here is the link to a copy of the workbook.

https://drive.google.com/open?id=1esNcOsI8wJiceNYW2FH9K-DsMIRf0z49

 

[mumps]

Place this macro in the code module for cmdSaveItem command button.

Private Sub cmdSaveItem_Click()
    Dim ctrl As Control, LastRow As Long
    LastRow = Sheets("Item").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            If ctrl.Value = "" Then
                MsgBox ("Please enter a value for " & ctrl.Name)
                ctrl.SetFocus
                Exit Sub
            End If
        End If
    Next ctrl
    Sheets("Item").Cells(LastRow, 1).Resize(1, 7) = Array(txt_ItemName.Value, _
        txt_ProdCodeSKU.Value, txt_VendorSelection.Value, txt_Location, txt_MaxStock.Value, txt_ReorderLevel.Value, txt_ItemStocked.Value)
End Sub

 

[Channce]

Mumps, you're a life saver! Upon saving, can we also clear the data from the form?

It seems to be overwriting on the same line on every save rather than entering new data to the next line.

 

[mumps]

Try:

Private Sub cmdSaveItem_Click()
    Dim ctrl As Control, LastRow As Long
    LastRow = Sheets("Item").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            If ctrl.Value = "" Then
                MsgBox ("Please enter a value for " & ctrl.Name)
                ctrl.SetFocus
                Exit Sub
            End If
        End If
    Next ctrl
    Sheets("Item").Cells(LastRow + 1, 1).Resize(1, 7) = Array(txt_ItemName.Value, _
        txt_ProdCodeSKU.Value, txt_VendorSelection.Value, txt_Location, txt_MaxStock.Value, txt_ReorderLevel.Value, txt_ItemStocked.Value)
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
            ctrl.Value = ""
        End If
    Next ctrl
    txt_ItemStocked = False
End Sub