I'm sure this can be done somehow

R

Robert Byrne

I'm sure this can be done somehow...I just don't know how to do it.

I currently have two worksheets in a workbook. One is a product price list.
The second is an invoice. The invoice has drop downs for quantity and
product. The unit price is pulled from the product price list using an
IF(ISNA(VLOOKUP)) function. It calcs the subtotal, tax, and total. What
I'd like to do is also subtract the quantity for the product selected on the
invoice from an "In Stock" total on the price list, essentially updating my
inventory when an invoice is created.

Any Ideas?

Robert

P.S. I follow directions well.
 
B

Bob Phillips

Robert,

You can do it with event code.

A few assumptions that you will have to adjust in the code
- the product worksheet is called Product
- the product list is in column G
- the instock list is in column K
- the invoice worksheet is called Invoice
- the product dropdown is in G10
- the quantity dropdown is in H10

Private Sub Worksheet_Change(ByVal Target As Range)
Dim iRow As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Target.Address(False, False) = "H10" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Product").Range("G:G"), 0)
Worksheets("Product").Range("K" & iRow).Value = _
Worksheets("Product").Range("K" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.




--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

Robert Byrne

Wow Bob! That was Fast! Thanks!

I have multiple lines that allow for selection of multiple items on the
invoice. How do I alter the code to accommodate this?

Robert
 
B

Bob Phillips

Robert,

Change the line

If Target.Address(False, False) = "H10" Then

to either

If Not Intersect(Target, Me.Range("H5:M20")) Is Nothing Then

if the cells are contiguous, or

If Not Intersect(Target, Me.Range("H5:H20,J7:J9,K11,M1")) Is Nothing
Then

if the cells are not contiguous.


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top