Subtract cell value based on textbox

qontrol · Aug 28th 2017

Hello everyone,

I have a userform which has a listbox with 3 columns (Type, Size and Inventory) and a few textboxes. The listbox gets its values from a main table.

1. I want to select multiple items from the listbox and add it to a new worksheet called "Data". Also the Textbox values need to added there.
2. Every time I add something it has to place values beneath the last used row.
3. Every time I add an amount to "Data" it has to subtract that textbox value from the main table's Inventory on worksheet "Inventory".
4. When the inventory of an item is 0 it needs to show a message.

Is this possible? It seems quite a large project.
I have an example sheet in attachment.forum.ozgrid.com/index.php?attachment/73689/

I am still learning VBA

Thank you!

Max1616 · Aug 28th 2017

Re: Subtract cell value based on textbox

Hello! Create a command button for your user form (to run the code when clicked) and then paste in this code to the userform module:

Code

Private Sub CommandButton1_Click()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim i As Long
Dim j As Long
Dim lrow As Long
Dim frow As Long
Dim MType As String
Dim MSize As String
Dim MInv As String
Dim MDate As Date
Dim MAmount As Double
Dim MCustomer As String


Set ws1 = Sheets("Inventory")
Set ws2 = Sheets("Data")


MDate = TextBox1
MCustomer = TextBox2
MAmount = TextBox3


frow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1


For i = 1 To Me.ListBox1.ListCount
    lrow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
    If ListBox1.Selected(i - 1) Then
        MType = ListBox1.List(i - 1, 0)
        MSize = ListBox1.List(i - 1, 1)
        MInv = ListBox1.List(i - 1, 2)
        ws2.Cells(lrow, 1) = MDate
        ws2.Cells(lrow, 2) = MCustomer
        ws2.Cells(lrow, 3) = MType
        ws2.Cells(lrow, 4) = MSize
        ws2.Cells(lrow, 5) = MAmount
    End If
Next i


For i = frow To lrow
    For j = 2 To ws1.Cells(Rows.Count, 1).End(xlUp).Row
        If ws1.Cells(j, 1) = ws2.Cells(i, 3) And ws1.Cells(j, 2) = ws2.Cells(i, 4) Then
            ws1.Cells(j, 3) = ws1.Cells(j, 3) - ws2.Cells(i, 5)
            If ws1.Cells(j, 3) <= 0 Then MsgBox ("This entry will cause " & ws1.Cells(j, 1) & " size " & ws1.Cells(j, 2) & " to be at " & ws1.Cells(j, 3) & " inventory.")
        End If
    Next j
Next i


Unload Me
End Sub

Display More

I hope this helps!

-Max

qontrol · Aug 29th 2017

Re: Subtract cell value based on textbox

Hi Max,

I'm going to give this a try when I come home!

Thank you!

Subtract cell value based on textbox

Participate now!

Share