Trying to create a GUI that can save input values and output values based on inputs into a sheet

[Deleted]

Hi all, 

I'm completely new to working with VBA. I work in a rat lab and I'm trying to create a GUI that will allow a user to input three things: rat number, how much they weight, and how much food they've received that day already. Based on those values, it would output how much more they're supposed to be fed that day.

So far the code that I have does not seem to be working. Any help would be appreciated!

Private Sub Label1_Click()

End Sub

Private Sub ExitCmd_Click()
'Close PredictFeed
Unload Me

End Sub

Private Sub FeedAmount_Change()
'Will output a value to the sheet "LookupLists" under the column "FeedAmt" (column D)
'This value is 300 minus whatever food they've already recieved! Based on FoodReceived (below)

End Sub

Private Sub FoodReceived_Change()
'Allow users to enter the amount of food a rat recieved that day and save it to the sheet "LookupLists" under the column "FoodRecieved" (column C)

End Sub

Private Sub SaveCmd_Click()
Dim answer As String, filename As Object
answer = MsgBox("SAVE this value for today?", vbQuestion + vbYesNo)
If answer = vbNo Then Exit Sub
If answer = vbYes Then
Dim rng As Range
Set rng = ThisWorkbook.Sheets("LookupLists").Range("B2")
rng.Select
Application.WindowState = xlMinimized

End If
End Sub

Private Sub UserForm_Click()

End Sub

Private Sub cmdAdd_Click()
'Copy input values to sheet
'Select a rat from rats 1-28
Dim oNewRow As ListRow
Dim rng As Range
Set rng = ThisWorkbook.Worksheets("LookupLists").Range("RatIDTable")
rng.Select
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
With ws
oNewRow.Range.Cells(1, 1).Value = Me.cboRatID.Value

End With
'Clear input controls
Me.cboRatID.Value = ""

End Sub

Private Sub Weight_Change()
'Allow users to enter the rats weight for that day and save it in the sheet "LookupLists", under the column "Weight" (B)
ActiveWorkbook.Sheets("LookupLists").Activate
Range("B2").Select
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Export Data to worksheet
Cells(emptyRow, 2).Value = Weight.Value

'If cboCategory = "Select Category..." Or cboCategory = "" Or txtrecipename = "" Or txtsource = "" Or txtpage = "" Then
'MsgBox "All Fields are Required!"
'Exit Sub
'End If

End Sub

(Photos for reference).

[#2 · Accepted solution]

Hey Laura-

Hope that you're doing well.  Maybe you could try doing something like this (See attached file for reference):

Private Sub cmdExit_Click()
    'Close PredictFeed
    Unload Me
End Sub

Private Sub cmdSave_Click()

    Dim lo As ListObject
    Dim ws As Worksheet
    Dim arrData As Variant
    Dim NewLstRow As ListRow

    'Int() forces an Integer CDbl() forces a Double
    arrData = Array(Date, Int(Me.cboRatNum), CDbl(Me.txtWeight), CDbl(Me.txtFoodRec))
    
    'Define worksheet
    Set ws = ActiveSheet

    ' Get reference to table
    Set lo = ws.ListObjects("tblRatData")  ' <--- Update this with your table name

    'Add List Object Row
    Set NewLstRow = lo.ListRows.Add(AlwaysInsert:=True)
   
    'Put Data in Row
    NewLstRow.Range = arrData
    
End Sub

Private Sub txtFoodRec_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    With Me
        If .cboRatNum.Value = "" Or .txtWeight = "" Or .txtFoodRec = "" Then
            'Do Nothing...
            Exit Sub
        Else
            'Update Feed Amount in Form when last value has been filled in
            .txtFeedAmount = 300 - CDbl(.txtFoodRec)
        
        End If
    End With
End Sub

Private Sub UserForm_Initialize()

    Dim intLp As Integer
    
    'Load cboRatNumber with values....
    For intLp = 1 To 28
        Me.cboRatNum.AddItem intLp
    Next intLp

End Sub

[#3]

Matt, 

Thanks very much for your help with fixing my code. This works great.