Adding or subtracting a quantity of 1 from an inventory using command buttons

[gds3781]

Help

I have a spreadsheet with an inventory table on sheet2. It's a 104RX5C table named Inventory and so is the sheet with the following columns: and

Family

Model

Volume

PartNumber

Quantity

<tbody>
</tbody>

On Sheet1 "Data Entry " i have a Userform that has 3 comboboxes corresponding to family, model and Volume; named "cbo1,cbo2,cbo3" and a "-" & "+" command button named cmdAdd & cmdSubtract to add +1 quantity to the corresponding item on the 3 combo boxes.

I have managed to get the 3 combo boxes working and dependent on each other, I just need help with the +1 or -1 buttons to change the quantity of the corresponding item.

I haven't got the slightest idea on how to code that in VBA.

Cheers,
Guenter

 

[mgd_Excel]

Help

I have a spreadsheet with an inventory table on sheet2. It's a 104RX5C table named Inventory and so is the sheet with the following columns: and

Family

Model

Volume

PartNumber

Quantity

<tbody>
</tbody>

On Sheet1 "Data Entry " i have a Userform that has 3 comboboxes corresponding to family, model and Volume; named "cbo1,cbo2,cbo3" and a "-" & "+" command button named cmdAdd & cmdSubtract to add +1 quantity to the corresponding item on the 3 combo boxes.

I have managed to get the 3 combo boxes working and dependent on each other, I just need help with the +1 or -1 buttons to change the quantity of the corresponding item.

I haven't got the slightest idea on how to code that in VBA.

Cheers,
Guenter

With a little research you should be able to solve this problem:
convert your inventory data to a table:
You will then use the listobject to reference this data.
Of course you will need to establish a lookup field in your table values = Family&Model&Volume
lookup value = cbo1.value&cbo2.value.cbo3.value
Your script will refernce the table, search the rows for the matching row, then reference the quantity field
Once found increase quantity field value by +1 (or -1 depending on button pressed)
you will then refresh the form
Have a go, once you present something you will get a lot help to complete.

 

[gds3781]

how would i set up this lookup field? what commands do i need?

 

[gds3781]

I managed to get this far but I frankly know absolutely nothing about VBA

Private Sub cmdAdd_Click()    Dim x As Long
    Dim ws As Worksheet
    Set ws = Worksheets("Inventory")
    
With ws
    .Cells(x, 1).Value = ComboBox1.Value
    .Cells(x, 2).Value = ComboBox2.Value
    .Cells(x, 3).Value = ComboBox3.Value
    














    ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
End Sub