Why not have a transaction sheet

initialize on hand stock
Stock Item1 50
Stock Item2 100
Stock Item3 25
Stock Item4 10

Now transactions are added on each line
Stock Item1 -1
Stock Item2 2
Stock Item2 -1
Stock Item3 8
Stock Item1 -2

then on your original sheet have your stock item listed in column 1. In
column2

=Sumif(Transaction!A:A,A2,Transaction!B:B)

and drag fill down.

You could of course reverse the order, and insert a line at the top when
adding a transation. Additionally, you might put in a date column.

This gives you a history. If you have thousands of items, then it probably
isn't feasible, but then using excel probably isn't the best choice either.

If you want to go with what you describe, in the sheet module where you have
commandbutton1 (to increase) and commandbutton2 (to decrease) - entries are
made in A2:B2 of that sheet.

Increment:

Private Sub CommandButton1_Click()
Dim rng as Range, res as Variant
set rng = Range(cells(3,1),cells(rows.count,1).End(xlup))
res = application.Match(Range("A2").Value,rng,0)
if not iserror(res) then
rng(res).offset(0,1).Value = rng(res).offset(0,1).Value _
+ Range("B2").Value
Range("A2:B2").Clear contents
else
msgbox Range("A2").Value & " not found"
End if


Decrement:

Private Sub CommandButton2_Click()
Dim rng as Range, res as Variant
set rng = Range(cells(3,1),cells(rows.count,1).End(xlup))
res = application.Match(Range("A2"),Value,rng,0)
if not iserror(res) then
rng(res).offset(0,1).Value = rng(res).offset(0,1).Value _
- Range("B2").Value
Range("A2:B2").Clear contents
else
msgbox Range("A2").Value & " not found"
End if

--
Regards,
Tom Ogilvy




"Chacky" wrote:

Does anyone have a macro for simple stock control?

Here is how I envisage it working

The worksheet would have 2 columns - column 1 with a stock code, column 2
with a quantity.

The quantity in column 2 needs to be updated when stock is added or taken
away.

I think 2 macros would be required, 1 to add to stock and 1 to remove from
stock.

The stock code and quantity to be added or removed would be entered in
specific cells at the top of the worksheet.

The relevant macro (adding or removing stock) would be run, the macro would
search for the stock code in column 1 and adjust the quantity in column 2.

Doe this sound possible?