0

I am trying to build a tool tracking application where users can check in/check out tools at the required quantity. So far I have written the code for the check-in of tools which has Name, Date, Tool, Quantity, Job, and Condition. Whatever is entered auto fills the next empty row on the inventory sheet with this information.

Now I want to create a check out sheet with the same information, but I want it to subtract the quantities from the inventory, and not allow the user to check out if there is not enough currently available through data validation.

My first attempt was to count the number of occurrences of the tool in the list using the counting function. However, the issue I am having is that the occurrences of the tool name does not correlate directly with the actual quantity. This is because a user can enter various number of quantities.

I want to create from all the raw data from the entries a separate count of the current quantity of tools in inventory. Any help would be greatly appreciated and I am happy to answer any clarifying questions. Thank you!

Here is all my code:

Option Explicit

Function ValidateForm() As Boolean

    txtName.BackColor = vbWhite
    txtDate.BackColor = vbWhite
    cmbTool.BackColor = vbWhite
    txtQuantity.BackColor = vbWhite
    cmbJob.BackColor = vbWhite
    cmbCondition.BackColor = vbWhite
    
    ValidateForm = True
    
    If Trim(txtName.Value) = "" Then
    
        MsgBox "Name can't be left blank.", vbOKOnly + vbInformation, "Name"
        txtName.BackColor = vbRed
        txtName.Activate
        ValidateForm = False
    ElseIf Trim(txtDate.Value) = "" Then
        
        MsgBox "Date can't be left blank.", vbOKOnly + vbInformation, "Date"
        txtDate.BackColor = vbRed
        txtDate.Activate
        ValidateForm = False
        
    ElseIf Trim(txtQuantity.Value) = "" Then
        
        MsgBox "Quantity can't be left blank.", vbOKOnly + vbInformation, "Quantity"
        txtQuantity.BackColor = vbRed
        txtQuantity.Activate
        ValidateForm = False
    End If
        
    
End Function
Function Reset()

    Application.ScreenUpdating = False
    
    txtName.Value = ""
    txtName.BackColor = vbWhite
    
    txtDate.Value = ""
    txtDate.BackColor = vbWhite
    
    cmbTool.Text = ""
    cmbTool.BackColor = vbWhite
    
    txtQuantity.Value = ""
    txtQuantity.BackColor = vbWhite
    
    cmbJob.Text = ""
    cmbJob.BackColor = vbWhite
    
    cmbCondition.Text = ""
    cmbCondition.BackColor = vbWhite
    
    Application.ScreenUpdating = True
    
    
End Function
Private Sub cmbReset_Click()
    
    Dim i As Integer
    
    i = MsgBox("Do you want to reset this form?", vbQuestion + vbYesNo + vbDefaultButton2, "Form Reset")
    
    If i = vbYes Then
        
        Call Reset
        
    End If
    
End Sub
Private Sub Save_Click()

    Application.ScreenUpdating = False
    
    Dim iRow As Long
    
    iRow = Sheets("Inventory").Range("A1048576").End(xlUp).Row + 1
    
    If ValidateForm = True Then
        
        With ThisWorkbook.Sheets("Inventory")
        
            .Range("A" & iRow).Value = iRow - 1
            .Range("B" & iRow).Value = txtName.Value
            .Range("C" & iRow).Value = txtDate.Value
            .Range("D" & iRow).Value = cmbTool.Text
            .Range("E" & iRow).Value = txtQuantity.Value
            .Range("F" & iRow).Value = cmbJob.Text
            .Range("G" & iRow).Value = cmbCondition.Text
            
        End With
        Call Reset
    Else
        Application.ScreenUpdating = True
        Exit Sub
    End If
        
        Application.ScreenUpdating = True
            
End Sub
2
  • Hello Lukas, all of your code appears to be resetting a number of named ranges on your sheet or entering some values... I cannot see anything attempting to produce a count? From your text I gather you have tried to count the number of unique occurrences of a tool, however I cannot quite understand what the problem is with the quantity.. Is this referring to the txtQuantity value? Can you clarify what the ranges are and how they could be used to calculate the total? Jun 26, 2020 at 2:24
  • @Lukas Sievert,, in last code SAVE,, U have current quantity of Tool in Column E,, now the instant solution is,,, U need to make 2 Columns nxt to it,, 1st for SOLD/USED and 2nd for Current Qty (Col E)- SOLD qty (ColF),, you get Qty in hand in Col G. Or read SOLD Qty from Sheet to User form Txt box and use formula to get Qty in hand and display on Form as well update on Sheet also. Jun 26, 2020 at 4:50

1 Answer 1

0

You could use WorksheetFunction.SumIf(testedrange,criteria,sumrange).

If column D is the tool's name, then testedrange is Column D, criteria is cmbTool.Text, and sumrange is Column E. Guessing that Row 1 is the headers for the table, then the formula would be: WorksheetFunction.SumIf(.Range("D2:D") & CStr(iRow),cmbTool.Text,.Range("E2:E" & CStr(iRow))

I would strongly recommend, though, that you "Format List as Table". It will greatly ease your coding and your worksheet management. The default table name will be "Table1", but you can change it in the Excel Table Design Ribbon on the far left of the linked image (it's still "Table1" in the image). Let's say you give it a name like "CheckOuts", and that your column headers were (A to G), "EntryNbr", "UserName", "Date", "Tool", "Qty", "Job", and "Condition".

Your Save_Click() code could be

Private Sub Save_Click()
Application.ScreenUpdating=False
With ThisWorkbook.Sheets("Inventory").ListObjects("CheckOuts").ListRows.Add
    .Range(1,1).Value = .Range.Row-1
    .Range(1,2).Value = txtName.Value
    .Range(1,3).Value = txtDate.Value
    .Range(1,4).Value = cmbTool.Text
    .Range(1,5).Value = txtQuantity.Value
    .Range(1,6).Value = cmbJob.Text
    .Range(1,7).Value = cmbCondition.Text
End With
Call Reset
Application.ScreenUpdating=True
End Sub

You would not need to track your iRow variable, since the added ListRow is always exactly 1 row, and your .Range(1,X) points to the only row in that ListRow.

Your validation could subtract the current "checked out" quantity of the tool using WorkSheetFunction.SumIf([CheckOuts[Tool]],cmbTool.Text,[CheckOuts[Qty]])

[CheckOuts[Tool]] is the "Structured Reference" to all the data cells in the "Tool" column that feeds the function. Likewise, [CheckOuts[Qty]] is the structured reference for the quantities checked out. The function then sums the Qty values for all rows where Tool matches the cmbTool.Text. Subtract it from your total available (your code did not give an indication of where/how you track the total number of each tool), and you can deny the request if txtQuantity.Value exceeds the difference.

Private Sub txtQuantity_AfterUpdate()
    Dim lAvailable as Long
    
    If txtQuantity.value<>"" Then 
        lAvailable = {Total # Tools} _
            - WorkSheetFunction.SumIf( _
                [CheckOuts[Tool]] _
                , cmbTool.Text _
                , [CheckOuts[Qty]])
        If  lAvailable < txtQuantity.Value Then
            MsgBox "Choose " & lAvailable & " or Fewer", vbOKOnly+vbCritical,"Too Many Requested"
            'Turn off events to prevent looping the Change event
            Application.EnableEvents=False
            'Save the user some typing
            txtQuantity.Value = lAvailable
            'Turn events back on
            Application.EnableEvents=True
        End If
    End If
    'See the following text explanation for this line in the sub
    Save.Enabled=(txtName.Value<>"" and txtDate.Value<>"" and txtQuantity.Value<>"")

End Sub

I'll go out on a limb and say you track the different tool totals in another table named, "Totals" with the tool name in the first column (Tool) and total quanity in the second (Qty) ... then the {Total # Tools} value could be calculated with WorksheetFunction.SumIf([Totals[Tool]],cmbTool.Text,[Totals[Qty]])

And if you really want to make it easy and you have a label for your Quantity input box (, add:

Private Sub cmbTool_AfterUpdate()
{label name}.Caption = "Quantity"
If cmbTool.Text<>"" Then
    {label name}.Caption = {label name}.Caption _
        & " (" _
        & WorksheetFunction.SumIf([Totals[Tool]],cmbTool.Text,[Totals[Qty]]) _
        - WorkSheetFunction.SumIf([CheckOuts[Tool]],cmbTool.Text,[CheckOuts[Qty]]) _
         & "maximum)"
End If
End Sub

Finally, you could add:

Private Sub *control*_AfterUpdate()
    Save.Enabled=(txtName.Value<>"" and txtDate.Value<>"" and txtQuantity.Value<>"")
End Sub

Where control is each of the two other form objects, txtName and txtDate (you'd have one sub then, for each of the three, but with their names in place of control). Then the user cannot click the Save button until all three fields have values. You could eliminate, then, your validation function.

You'd also need, then

Private Sub UserForm_Activate()
    Save.Enabled=False
End Sub

so that the Save button starts off disabled.

Again, having the list "Formatted as a table" greatly simplifies your coding so you don't have to try to track how many lines are in the table!

Of course, all the code needs to be in the UserForm's code page (your Sub names in the code you supplied appear to be such)

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .