Inventory Management with User Forms

I have been using MS-Excel but haven’t done much programming in Excel VBA. I know how to place controls on a user-form and do some simple coding.
Can you show me how to create a simple user-form in Excel that can help in inventory management? I would prefer to have the same data on an Excel worksheet also. Is that possible?
Thank you for your help in advance 🙂
We have created a user-form with appropriate controls and proper VBA coding for inventory management:

Enter a new or old item
Check for availability in stock for already existing items or buy new items
Quantity sold cannot be more than available quantity in stock
You can’t leave the sales or purchase quantity empty
Automatic calculations of sales, purchase and balance quantities
Transfer of sales and purchase data automatically to the Excel worksheet along with the dates
You can use such forms to enter data using bar-code machines

Watch the video below and study the code to learn how to use a user-form for inventory management in MS-Excel:


The code for each control is given below:
Code for checking availability of item:
Private Sub CommandButton1_Click()
x = 2
Dim qty As Integer
qty = 0
Do While Cells(x, 1) <> “”
If TextBox1.Text = Cells(x, 1) Then
TextBox5.Value = Cells(x, 3) – Cells(x, 2)
qty = qty + TextBox5.Value
End If
x = x + 1
Loop
TextBox5.Value = qty
MsgBox “Now select your sale or buy option”
End Sub

Code for sale:

Private Sub CommandButton2_Click()
If Val(TextBox2) > Val(TextBox5) Or TextBox2.Value = “” Then
MsgBox “Sale qty greater than available stock or empty!”
TextBox2.Text = “”
TextBox2.SetFocus
Else
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Value
Cells(erow, 2) = TextBox2.Value
Cells(erow, 4) = TextBox4.Value
End If

End Sub

Code for buy or purchase:
Private Sub CommandButton5_Click()
If TextBox2.Value = “” Then
MsgBox “Buy value cannot be empty!”
TextBox2.SetFocus
Else
erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Value
Cells(erow, 3) = TextBox2.Value
Cells(erow, 4) = TextBox4.Value
End If
End Sub

Code for ‘reset’:
Private Sub CommandButton3_Click()
Dim objCtrl As Control
For Each objCtrl In Me.Controls
If Left(objCtrl.Name, 4) = “Text” Then objCtrl.Value = “”
OptionButton1.Value = False
OptionButton2.Value = False
Next
OptionButton1.Visible = True
OptionButton2.Visible = True
CommandButton1.Visible = True
CommandButton2.Visible = True
CommandButton4.Visible = True
CommandButton5.Visible = True
TextBox1.SetFocus
End Sub

Code for closing the form:
Private Sub CommandButton4_Click()
Unload Me
End Sub

Code for the sale and buy option buttons
Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then
OptionButton2.Visible = False
CommandButton5.Visible = False
TextBox2.SetFocus
End If
End Sub

Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then
OptionButton1.Visible = False
CommandButton2.Visible = False
TextBox2.SetFocus
End If
End Sub

Code when the form ‘initializes’:
Private Sub UserForm_Initialize()
x = 2
Do While Cells(x, 1) <> “”
Cells(x, 5) = Cells(x, 3) – Cells(x, 2)
x = x + 1
Loop
OptionButton1.Value = False
OptionButton2.Value = False
TextBox1.SetFocus
End Sub

VBA code when the form is ‘activated’:
Private Sub UserForm_Activate()
MsgBox “Enter an item number and check availability of stock qty before sale or purchase or enter a new item for purchase!”
End Sub

Of course the Excel VBA code or macro can be further optimized depending on your requirements. The main idea here is to learn more about the different user-form controls and code them properly to perform the desired actions.

One thought on “Inventory Management with User Forms

  1. Rodel D. Miñoza

    good day sir! why is it that the code for checking the available quantity particularly the “-” code will not be run and the VB editor would recognized it as an error.? please reply me in my email. thank you

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *