Inventory Management with UserForms

Inventory management for small business is important. Inventory management with Userforms in Microsoft Excel is easy and economical. Using a few forms for suppliers details, purchases, customer details and sales, we can automatically create a stock-in-hand data that tells us about our inventory. Since most small businesses have MS-Office installed on their laptops, PCs or even mobiles, the inventory management becomes a viable option.

Watch the video below:

 

watch this video on YouTube.

Here’s a picture of the interface:

Inventory Management with UserForms
Inventory Management with UserForms

Here’s the complete VBA code:

Sub showAddSupplierForm()
frmSupplier.Show
End Sub

Sub viewStock()
frmStock.Show
End Sub

Sub addCustomers()
frmCustomer.Show
End Sub

Sub addPurchases()
frmPurchases.Show
End Sub

Sub addSales()
frmSales.Show
End Sub

Private Sub cmdAddCustomer_Click()
Dim erow As Long
erow = Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
‘MsgBox “the erow number is ” & erow
Sheet3.Cells(erow, 1) = Me.TextBox1
Sheet3.Cells(erow, 2) = Me.TextBox2
Sheet3.Cells(erow, 3) = Me.TextBox3
Sheet3.Cells(erow, 4) = Me.TextBox4
Sheet3.Cells(erow, 5) = Me.TextBox5
Sheet3.Cells(erow, 6) = Me.TextBox6
Sheet3.Cells(erow, 7) = Me.TextBox7
Sheet3.Cells(erow, 8) = Me.TextBox8
Sheet3.Cells(erow, 9) = Me.TextBox9
If optMale Then
Sheet3.Cells(erow, 10) = Me.optMale.Caption
End If
If optFemale Then
Sheet3.Cells(erow, 10) = Me.optFemale.Caption
End If

If chkOffer Then
Sheet3.Cells(erow, 11) = “Yes”
Else
Sheet3.Cells(erow, 11) = “No”
End If

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
If TypeName(ctl) = “CheckBox” Or TypeName(ctl) = “OptionButton” Then
ctl.Value = “0”
End If
Next ctl
End Sub

 

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdPurchases_Click()
Dim erow As Long, i As Long, lastrow As Long

lastrow = Sheet7.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

erow = lastrow + 1
If ComboBox1.Value = “” Then
MsgBox “Select a supplier!”
ComboBox1.SetFocus
Exit Sub
End If

On Error Resume Next

Sheet7.Cells(erow, 1) = Me.ComboBox1.Value
Sheet7.Cells(erow, 2) = Me.TextBox1
Sheet7.Cells(erow, 3) = Me.TextBox2
Sheet7.Cells(erow, 4) = Me.ComboBox2
Sheet7.Cells(erow, 5) = Me.TextBox3.Value
Sheet7.Cells(erow, 6) = Me.TextBox4.Value
Sheet7.Cells(erow, 7) = Me.TextBox3.Value * Me.TextBox4.Value
erow = erow + 1
Sheet7.Cells(erow, 4) = Me.ComboBox3.Value
Sheet7.Cells(erow, 5) = Me.TextBox5.Value
Sheet7.Cells(erow, 6) = Me.TextBox6.Value
Sheet7.Cells(erow, 7) = Me.TextBox5.Value * Me.TextBox6.Value
erow = erow + 1
Sheet7.Cells(erow, 4) = Me.ComboBox4.Value
Sheet7.Cells(erow, 5) = Me.TextBox7.Value
Sheet7.Cells(erow, 6) = Me.TextBox8.Value
Sheet7.Cells(erow, 7) = Me.TextBox7.Value * Me.TextBox8.Value
erow = erow + 1
Sheet7.Cells(erow, 4) = Me.ComboBox5.Value
Sheet7.Cells(erow, 5) = Me.TextBox9.Value
Sheet7.Cells(erow, 6) = Me.TextBox10.Value
Sheet7.Cells(erow, 7) = Me.TextBox9.Value * Me.TextBox10.Value
erow = erow + 1
Sheet7.Cells(erow, 4) = Me.ComboBox6.Value
Sheet7.Cells(erow, 5) = Me.TextBox11.Value
Sheet7.Cells(erow, 6) = Me.TextBox12.Value
Sheet7.Cells(erow, 7) = Me.TextBox11.Value * Me.TextBox12.Value

 

End Sub

Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Or TypeName(ctl) = “ComboBox” Then
ctl.Value = “”
End If
Next ctl
End Sub

Private Sub ComboBox6_Change()

End Sub

Private Sub UserForm_Initialize()

Dim i As Long, lastrowf As Long, lastrow As Long

Sheet2.Select

lastrowf = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

For i = 2 To lastrowf
Me.ComboBox1.AddItem Sheet2.Cells(i, “A”).Value
Next i

Sheet7.Select
lastrowf = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

 

For i = 2 To lastrowf
For x = 2 To 6
Me.Controls(“Combobox” & x).AddItem Sheet7.Cells(i, 4).Value
Next x
Next i

lastrow = Sheet7.Range(“B” & Rows.Count).End(xlUp).Row
MsgBox “The last row in column B is ” & lastrow
Me.TextBox1 = Cells(lastrow, 2) + 1

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “ComboBox” Or TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
Next ctl

End Sub

Private Sub cmdSales_Click()
Dim erow As Long, i As Long, lastrow As Long

lastrow = Sheet6.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
erow = lastrow + 1

If ComboBox1.Value = “” Then
MsgBox “Select a customer!”
ComboBox1.SetFocus
Exit Sub
End If

On Error Resume Next

Sheet6.Cells(erow, 1) = Me.ComboBox1.Value
Sheet6.Cells(erow, 2) = Me.TextBox1
Sheet6.Cells(erow, 3) = Me.TextBox2
Sheet6.Cells(erow, 4) = Me.ComboBox2.Value
Sheet6.Cells(erow, 5) = Me.TextBox3.Value
Sheet6.Cells(erow, 6) = Me.TextBox4.Value
Sheet6.Cells(erow, 7) = Me.TextBox3.Value * Me.TextBox4.Value
erow = erow + 1
Sheet6.Cells(erow, 4) = Me.ComboBox3.Value
Sheet6.Cells(erow, 5) = Me.TextBox5.Value
Sheet6.Cells(erow, 6) = Me.TextBox6.Value
Sheet6.Cells(erow, 7) = Me.TextBox5.Value * Me.TextBox6.Value
erow = erow + 1
Sheet6.Cells(erow, 4) = Me.ComboBox4.Value
Sheet6.Cells(erow, 5) = Me.TextBox7.Value
Sheet6.Cells(erow, 6) = Me.TextBox8.Value
Sheet6.Cells(erow, 7) = Me.TextBox7.Value * Me.TextBox8.Value
erow = erow + 1
Sheet6.Cells(erow, 4) = Me.ComboBox5.Value
Sheet6.Cells(erow, 5) = Me.TextBox9.Value
Sheet6.Cells(erow, 6) = Me.TextBox10.Value
Sheet6.Cells(erow, 7) = Me.TextBox9.Value * Me.TextBox10.Value
erow = erow + 1
Sheet6.Cells(erow, 4) = Me.ComboBox6.Value
Sheet6.Cells(erow, 5) = Me.TextBox11.Value
Sheet6.Cells(erow, 6) = Me.TextBox12.Value
Sheet6.Cells(erow, 7) = Me.TextBox11.Value * Me.TextBox12.Value

End Sub

Private Sub ComboBox2_Change()
Dim lastrow As Long, i As Long
lastrow = Sheet7.Cells.Find(What:=”*”, _
After:=Range(“A1″), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
For i = 2 To lastrow
If ComboBox2.Value = Sheet7.Cells(i, 4) Then
TextBox4.Value = Cells(i, 6).Value
TextBox4.Value = TextBox4.Value + (TextBox4.Value) * 0.3
End If
Next i
End Sub

 

Private Sub UserForm_Initialize()

Dim i As Long, lastrow As Long

lastrow = Sheet3.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
For i = 2 To lastrow
Me.ComboBox1.AddItem Sheet3.Cells(i, “A”).Value
Next i

Sheet6.Select
lastrow = Sheet6.Range(“B” & Rows.Count).End(xlUp).Row

TextBox1.Value = Cells(lastrow, 2)
TextBox1.Value = TextBox1.Value + 1

For i = 2 To lastrowf
Me.ComboBox1.AddItem Sheet2.Cells(i, “A”).Value
Next i

Sheet7.Select
lastrowf = Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

 

For i = 2 To lastrowf
For x = 2 To 6
Me.Controls(“Combobox” & x).AddItem Sheet7.Cells(i, 4).Value
Next x
Next i

‘lastrow = Sheet7.Range(“B” & Rows.Count).End(xlUp).Row
‘MsgBox “The last row in column B is ” & lastrow
‘Me.TextBox1 = Cells(lastrow, 2) + 1

End Sub

Private Sub cmdClose_Click()
Unload Me
Sheet4.Select
End Sub

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Controls

If TypeName(ctl) = “ComboBox” Or TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If

Next ctl

End Sub

Private Sub cmdStock_Click()
Dim i As Long, totalqtypurchased As Long, totalqtysold As Long

Sheet7.Select
lastrow = Sheet7.Cells.Find(What:=”*”, _
After:=Range(“A1″), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

totalqtypurchased = o
For i = 2 To lastrow
If Sheet7.Cells(i, 4) = ComboBox2.Value Then
TextBox1.Value = Cells(i, 6).Value
totalqtypurchased = totalqtypurchased + Cells(i, 5).Value
End If
Next i

TextBox2.Value = totalqtypurchased

Sheet6.Select

lastrow = Sheet6.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

totalqtysold = o
For i = 2 To lastrow
If Sheet6.Cells(i, 4) = ComboBox2.Value Then
TextBox3.Value = Cells(i, 6).Value
totalqtysold = totalqtysold + Cells(i, 5).Value
End If
Next i

TextBox4.Value = totalqtysold

TextBox5.Value = totalqtypurchased – totalqtysold

If TextBox5.Value <= 0 Then
MsgBox “Not in stock”
TextBox6.Value = “REORDER”
ElseIf TextBox5.Value <= 6 Then
TextBox6.Value = “REORDER”
End If

End Sub

Private Sub cmdTransferData_Click()
lastrow = Sheet4.Cells.Find(What:=”*”, _
After:=Range(“A1″), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

erow = lastrow + 1

Sheet4.Cells(erow, 1) = ComboBox1.Value
Sheet4.Cells(erow, 2) = ComboBox2.Value
Sheet4.Cells(erow, 3) = TextBox1.Value
Sheet4.Cells(erow, 4) = TextBox2.Value
Sheet4.Cells(erow, 5) = TextBox3.Value
Sheet4.Cells(erow, 6) = TextBox4.Value
Sheet4.Cells(erow, 7) = TextBox5.Value
Sheet4.Cells(erow, 8) = TextBox6.Value

 

End Sub

 

Private Sub UserForm_Initialize()

Dim i As Long, lastrow As Long, x As Long

lastrow = Sheet2.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
For i = 2 To lastrow
Me.ComboBox1.AddItem Sheet2.Cells(i, “A”).Value
Next i

Sheet7.Select
lastrow = Sheet7.Cells.Find(What:=”*”, _
After:=Range(“A1”), _
LookAt:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row

For x = 2 To 6

Dim myCollection As Collection, cell As Range
On Error Resume Next
Set myCollection = New Collection

With Me.Controls(“Combobox” & x)
.Clear

For Each cell In Sheet7.Range(Cells(2, 4), Cells(lastrow, 4))
If Len(cell) <> 0 Then
Err.Clear
myCollection.Add cell.Value, cell.Value
If Err.Number = 0 Then .AddItem cell.Value
End If
Next cell
End With
Next x

End Sub

Private Sub cmdAddSupplier_Click()
Dim erow As Long, i As Long
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

For i = 1 To 9
If Me.Controls(“TextBox” & i) = “” Then
MsgBox “All data in the user-form must be entered!”
Controls(“TextBox” & i).SetFocus
Exit Sub
End If
Next i

If WorksheetFunction.CountIf(Sheet2.Range(“A:A”), Me.TextBox1.Value) > 0 Then
MsgBox “This Supplier already exists”
Exit Sub
Else
Sheet2.Cells(erow, 1) = Me.TextBox1
Sheet2.Cells(erow, 2) = Me.TextBox2
Sheet2.Cells(erow, 3) = Me.TextBox3
Sheet2.Cells(erow, 4) = Me.TextBox4
Sheet2.Cells(erow, 5) = Me.TextBox5
Sheet2.Cells(erow, 6) = Me.TextBox6
Sheet2.Cells(erow, 7) = Me.TextBox7
Sheet2.Cells(erow, 8) = Me.TextBox8
Sheet2.Cells(erow, 9) = Me.TextBox9
End If

End Sub

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub cmdReset_Click()
Dim ctl As Control
For Each ctl In Me.Controls
If TypeName(ctl) = “TextBox” Then
ctl.Value = “”
End If
Next ctl

End Sub

Download a sample file:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

7 thoughts on “Inventory Management with UserForms”

    1. I have problems with the coding at Userform_Initialize()
      Everytime i run the code it pops up the window show runtime error 13 – Type mismatch.
      Could anyone help me?

      Much thanks in advance.

  1. Dear Dinesh K Takyar,
    I ‘ve seen and learn many of its code from your Inventory Management with Userform, Sir I have some different requirement like I am using Handy BarCode Machine for receiving Goods for fast Data Entry, Please tell me how I can use in Excel VBA form and data will be save in quantity.
    Example : I am receiving multiple items from different Vendors and store it for further distribution to my Company other Department/Branches. And sometimes My Company Department/Branches return /handover the same items issued earlier for use but as for repair or maintenance it return to my company store, It means I have to update the quantity and replace the other item if stock available.
    Please help me in coding and VBA Form.
    I have Excel WorkBook named IMS-Excel (macro enabled)
    All Entries are by VBA Form
    Sheet1 :
    Links of Entry for Descriptions Details
    1. ITEMs List . 2.Department/Branches of Company, 3.Vendor
    Sheet2 :
    VBA Forms Detail of Items got from VENDOR in quantity with their Serial ID (Barcoded)
    Sheet3 :
    VBA forms Detail for Items Distribution to Department/Branches in Quantity after verifying Serial Number and for further Summary Report if generate for Item to Department-wise.
    Sheet4:
    VBA forms to generate Reports for:-
    1. Summary of Items in Store and Distributed and Availability of Items( Balance sheet)
    2. Summary of Items in Store if returned for any reasons.
    3. Summary of Items to Department-Wise
    4. Summary of Multiple Items received from Vendors

    I wish this requisite Inventory will be consider to teach through Video and Templates

    Thanking you for anticipation
    Regards
    Arsalan Khateeb
    arsalan.khateeb@gmail.com

Leave a Reply

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