cell change on another sheet

[gordsky]

Hi,

I have a workbook with numerous worksheets ranging from 1 - 30.
Sheet 1 contains stock levels for each item within column K

Staff enter info on the specific client sheets (sheet 2 - 30) which then deducts the stock ordered from sheet 1.

(ie so if a client on sheet 3 orders 10 units of an item the master stock level on sheet 1 deducts 10 from stock available for that item in column K)

What I am trying to achieve via vba is that if data is entered in sheet 2 - 30 and it causes a cell in sheet 1 column k to drop below 0 then a message box is displayed to that effect.

I can get it to work if the changes are made directly on sheet 1 but not if they are made on a different sheet.

Any help is appreciated.

 

[alansidman]

Show us the layouts of the two sheets so that we can design code that specifically meets your needs.

 

[JoeMo]

If you want it to work on Sheet1 you can't use a worksheet_change event for column K because the change(s) are being made on sheet(s) 2-30. You have two choices:

1. Use a worksheet_calculate event on sheet1. Each time the sheet calculates it checks for quantities at or below 0 and, if present, issues a message box.
2. Use a Thisworkbook sheet_change event that monitors sheets 2-30 for changes in stock quantities that result in at or below zero values in Sheet1.

 

[DanteAmor]

Hi @gordsky,

Some information is missing to complete the requirement:
- Column where the Items are on sheet1
- Column where you capture the item in the client sheet.
- Column where you capture the quantity on the client sheet.
- The quantity is always integer and greater than 0.