VB Code help to change/update existing code

Hi,

Thank you for all the help John (jaslake)

Further from my previous post (http://www.excelforum.com/excel-prog...ate-stock.html) there are a few changes and updates required to fix a design floor in the system (the code worked perfectly though).

Let me try to explain as plain and simple as i can.
The following headers should make sense once you open/see the sheet.

There are 2 stages/buttons...

Stock
When clicking the yellow yes button in the 'stock' section i would like the following to happen:
I have stated specific cells below however these would need to relate to the line with the button that is pressed.

1. Update C4 to minus amount of ordered stock in E4 (this is currently a formula but i think it will need to be a value as we will delete the data in E4 in the below steps)
Basically, we don't want to update B4 just yet as it is still in the building, we just want to show the amount of stock that isn't actually committed to an order effectively making it available to use.
2. Copy A4 to I4
3. Move D4 to J4
4. Move E4 to K4
5. Add current date/time to H4
6. Data sort H to L by Date (most recent at top, oldest at bottom)


Current Orders
When clicking the yellow yes button in the 'current orders' section i would like the following to happen:
I have stated specific cells below however these would need to relate to the line with the button that is pressed.

1. Update B4 to minus K4 (we now want to update B4 as the stock is completed/left the building)
2. Move H4:L4 to O4:S4
3. Data sort O to S by Date (most recent at top, oldest at bottom)
4. Now re-data sort H to L by Date (most recent at top, oldest at bottom)

I have attached the sheet.

Thank you,
Jamie

Hi Jamie

Try the Code in the attached...

Hi Jamie

Try the Code in the attached...

Hi Jamie

Try the Code in the attached...

Hi Jamie

Well, it wasn't a minor change but I think the attached does as you require. Let me know of issues.

Hi Jamie

Sorry about that.

In the Worksheet Change Event Code change the indicated Line.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Target.Column = 2 And Not Target.Column = 5 Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    Select Case Target.Column
    Case Is = 5
    
        '#### Change this line of Code ####
        Target.Offset(0, -2).Value = Target.Offset(0, -2).Value - Target.Value
        '#############################

    Case Is = 2
        Target.Offset(0, 1).Value = Target.Value
    End Select
    
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

You're welcome...glad I could help. Thanks for the "pseudo" Rep.

Hi Jamie

Pursuant to you're PM request, replace the CurrentOrders Code with this

Sub CurrentOrders()
    Dim myBtn As Shape
    Dim ws1 As Worksheet
    Dim cel As Range
    Dim LR As Long

    Set myBtn = ActiveSheet.Shapes(Application.Caller)
    Set ws1 = Sheets("THEORY")
    Set cel = myBtn.TopLeftCell

    Application.ScreenUpdating = False
    Application.EnableEvents = False
    With ws1
        If Not .Cells(cel.Row, 4).Value = "" And Not .Cells(cel.Row, 5).Value = "" Then

            LR = .Range("H" & .Rows.Count).End(xlUp).Row + 1
            .Cells(LR, 9).Value = .Cells(cel.Row, 1).Value
            .Cells(LR, 10).Value = .Cells(cel.Row, 4).Value
            .Cells(LR, 8).Value = .Cells(3, "U").Value
            .Cells(LR, 11).Value = .Cells(cel.Row, 5).Value
            .Cells(LR, "N").Value = cel.Row
            .Cells(cel.Row, "C").Value = .Cells(cel.Row, "C").Value - .Cells(cel.Row, "E").Value
            .Cells(cel.Row, "E").Value = ""
            .Cells(cel.Row, "D").Value = ""
            .Range("H3:N" & LR).Sort Key1:=.Range("H4"), Order1:=xlDescending, Header:=xlGuess, _
                    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
                    DataOption1:=xlSortNormal
        End If
    End With
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

The ChangeEvent Code behind Sheets THEORY should be entirely commented out or removed.

You're welcome...glad I could help.