VBA code not working anymore?

[David77]

Hello everybody,

I am currently managing a stock tracking file, which is edited manually to a large extent.

Currently, the four significant columns look like this:



Here, column I is the actual quantity in stock. If my code was functioning, which it was as of yesterday (and I have made no edits whatsoever to it), any number entered into column J should subtract that number from the quantity (so I4 should be 550 - 50 = 50 in the case above for example) after which the number also disappears from column J. The same applies for column K, where it just adds quantity rather than subtracts.

Finally, column L should track any point in time where the quantity changes in value (so we can see if any of the quantities have not been touched for longer periods of time).

This all worked as of yesterday, but as of this morning (central European time) none of these codes work anymore, neither the subtraction, addition or date & time columns.

My VBA code for this entire flow looks like this:



Can anybody assist me in figuring out what the problem is and how to potentially fix it so it all works again? It would be truly amazing and would really make my day!

Thank you so much everybody

Best regards,
David

 

[RoryA]

Is your sheet protected?

Also, just to confirm, did you remove the activex controls (if that's what they were)?

 

[David77]

Is your sheet protected?

Also, just to confirm, did you remove the activex controls (if that's what they were)?

Hello RoryA,

I am currently not using any command buttons at all, so I'm not sure if I have anything to remove at all.

My sheet is not protected currently no and this test sheet has never been protected

 

[RoryA]

Can you put the sample file on a sharing site (eg Onedrive or Dropbox) and post a link here so I can have a look?

 

[David77]

Can you put the sample file on a sharing site (eg Onedrive or Dropbox) and post a link here so I can have a look?

Hello Rory,

I actually fixed most of it now!

My code looks like this now:



You may copy paste and try it here if you want to:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range, c As Range
Set r = Intersect(Target, Columns("J:K"))
If Not r Is Nothing Then
    Application.EnableEvents = False
    For Each c In r.Cells
        With Cells(c.Row, "I")
            If IsNumeric(.Value) Then
                .Value = .Value + IIf(c.Column = 10, -c.Value, c.Value)
                c.ClearContents
            End If
        End With
    Next c
    Application.EnableEvents = True
End If
'Update 20140722
Dim WorkRng As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRng = Intersect(Application.ActiveSheet.Range("I:I"), Target)
xOffsetColumn = 4
If Not WorkRng Is Nothing Then
    Application.EnableEvents = False
    For Each Rng In WorkRng
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Now
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd-mm-yyyy, hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
    Application.EnableEvents = True
End If
End Sub

Now it (sort of) works, but the date ONLY shows when I manually change the value in column I. Is there a way to change this, so the date also updates, whenever the value in column I is changed by inserting an addition or subtraction to the value in column I through either column K or J?

Thank you!

 

[RoryA]

Other than changing the offset column number, I don't see how that code is different.

Also, we don't need a picture of your code, just the code itself - which should be pasted in code tags, please.