[Excel VBA] How to Prevent Actions When a Cell Value Exceeds a Limit

2025年12月27日

Overview

I am using Excel VBA to manage inventory. We had a rule: “Report if the total weight goes over 30g.” However, this rule was often ignored, causing problems.

So, I decided to enforce it with a system: “If the total is over 30g, do not allow the entry.”

Excel Configuration

The Excel sheet is set up as follows:

Column A: Item Name
Column B: Quantity
Column C: Weight (g)
Cell E1: Total Weight (Sum of g)
Next to E1: An “Inventory” button

The Code

Create a button labeled “Inventory” on Sheet1 and assign the following macro to it.

When you click this button, the code checks Cell E1.

If E1 > 30: A warning message appears, and nothing is written to the sheet.
If E1 <= 30: It adds “Inventory Checked” and “Today’s Date” to the last row of Column A.

Sub InventoryCheck()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim checkValue As Double

    ' Specify the worksheet (Change "Sheet1" if necessary)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Get the value from cell E1
    checkValue = ws.Range("E1").Value
    
    ' Check if value exceeds 30
    If checkValue > 30 Then
        MsgBox "The total weight exceeds 30g.", vbExclamation
    Else
        ' Get the last used row in Column A and add 1
        lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
        
        ' Enter "Inventory Checked" and the date in the last row
        ws.Cells(lastRow, 1).Value = "Inventory Checked " & Format(Date, "yyyy/mm/dd")
    End If
End Sub

Summary

By using this simple If statement to check a total value before processing, you can prevent human error and strictly enforce data entry rules. I hope this helps you with your inventory management.