I'm sure this can be done somehow

[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.

 

[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)

 

[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

 

[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)

 

[Robert Byrne]

Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert

 

[Bob Phillips]

Robert,

That was what I called it in my test. In practice, it is the activesheet so it is not referenced explicitly.

Do you to mail me the workbook?

--

HTH

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


Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert

 

[Robert Byrne]

It's on it's way.

Robert
Robert,

That was what I called it in my test. In practice, it is the activesheet so it is not referenced explicitly.

Do you to mail me the workbook?

--

HTH

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


Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert

 

[Robert Byrne]

Bob did you get it?
It's on it's way.

Robert
Robert,

That was what I called it in my test. In practice, it is the activesheet so it is not referenced explicitly.

Do you to mail me the workbook?

--

HTH

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


Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert

 

[Bob Phillips]

No I didn't. If you just reply, it won't work as I have a spam filter added.

Try

bob dot phillips at tiscali dot co dot uk

do the obvious with it.

--

HTH

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


Bob did you get it?
It's on it's way.

Robert
Robert,

That was what I called it in my test. In practice, it is the activesheet so it is not referenced explicitly.

Do you to mail me the workbook?

--

HTH

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


Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert

 

[Robert Byrne]

Bob....sent it that way...just removed the nothere from the address. It may be in your spam or Junk e-mail already... robert at odyssey dash consultants dot com

Robert
No I didn't. If you just reply, it won't work as I have a spam filter added.

Try

bob dot phillips at tiscali dot co dot uk

do the obvious with it.

--

HTH

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


Bob did you get it?
It's on it's way.

Robert
Robert,

That was what I called it in my test. In practice, it is the activesheet so it is not referenced explicitly.

Do you to mail me the workbook?

--

HTH

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


Bob I noticed in your first post you indicated variables that would need to be changed, notably

- the invoice worksheet is called Invoice


however this is not referenced in the code. Is there a mistype. I'm not having luck with the code as listed.

A few assumptions that you will have to adjust in the code


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) = "C19" Then
iRow = Application.Match(Target.Offset(0, -1).Value, _
Worksheets("Inventory").Range("G:G"), 0)
Worksheets("Inventory").Range("A" & iRow).Value = _
Worksheets("Inventory").Range("A" & iRow).Value - _
Target.Value
End If

ws_exit:
Application.EnableEvents = True
End Sub

Robert