Change event macro that triggers when a cell content is deleted either using keyboard or by right click


I have a change event macro that auto populates the date stamp when an action is selected from the dropdown menu.Likewise if the selected option is deleted it must trigger to delete the datestamp in the corresponding row.Is it possible to incorporate in the code?

Please ssist me on this.



Nice to see the code in the discussion to an asnswer that doesn't help you instead of the question, Dr Liss. Makes perfect sense. Your code writes the time stamp in one of 3 columns which is determined by where a match was found. If no match is found the cell can't be determined. Therefore any existing time stamp can't be removed. Perhaps, if you would add a copy of your workbook to your question (you can do that in Edit mode) we could understand why the time stamp must be in different columns and find a solution for your conundrum.
Variatus (rep: 4402) May 12, '20 at 6:57 am
Thank you!

I have attached a sample file.
Dr Liss (rep: 12) May 12, '20 at 1:21 pm
Add to Discussion


Selected Answer

Hello, Dr Liss

You have to declare a Public variable, lets say r as String, add a SelectionChange event procedure where you make r=target. That way, when you delete the cell (Change event) you already have the value of the cell when it was selected (previous value).

I have attached the file with these changes in the code in order you can try it.




I tried to delete the selected option from dropdown but I am getting run time error13: type mismatch for the line

 x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(r, Sheets("Key").Range("B1:B20"), 0)) 
Dr Liss (rep: 12) May 18, '20 at 1:26 am
I saw the problem. I think, it is fixed, now.

You made a choice from drop down menu and deleted the cell, did not select another cell before delete.
We can force this selection adding following rows:


Besides that, I added a condition over r, must be <>"".

Please, try it again. I changed the attached file.

Basilio (rep: 105) May 18, '20 at 2:00 am
It worked like a charm!! Thanks Basilio you are genius!
Dr Liss (rep: 12) May 19, '20 at 3:02 am
Add to Discussion

Use the Worksheet_Change even and call the desired macro to run from there.

Private Sub Worksheet_Change(ByVal Target As Range)

    MsgBox "Someone changed me!"

End Sub

Put this code in the worksheet where you need it to run, not in a module.



I have the code alreadybut it needs to be modified according to the requirement I have mentioned. It goes like this

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
Dim x As Long, c As Range
If Intersect(Target, Range("BK5:BM500")) Is Nothing Then Exit Sub
If Target.Value = "" Then Exit Sub
x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(Target.Value, Sheets("Key").Range("B1:B20"), 0))
If x >= 1 Then Cells(Target.Row, x + 48) = Date
End Sub
Dr Liss (rep: 12) May 12, '20 at 3:55 am
Add to Discussion

I have re-worked your code, made some changes and added some comments but the logic of your sheet doesn't support your desire. Rules must be invented before they can be written into code.

Right now, the user selects an action and a date gets written into the appropriate column. Then the user selects another action and another date gets written to another column. The effect is that columns AW:BJ document the progress of a process.

Now, if the user wouldn't select another step in the process but simply deletes whatever is written in BK:BM what should happen? Obviously, there shoud be different rules depending upon which of the 3 columns was cleared. Equally obvious is that there should be a safety mechanism to prevent data deletion due to a typing error.

It seems to me that there should be a logical flow, like in column BL: "image requested", "image processed", "image uploaded". You would probably want a mechanism that only permits deletion of the last done step. To enforce that you would first need a mechanism to prevent skipping steps. So, control goes to the date columns. Like, If an image wasn't requested, it can't be processed. But if someone deletes "processed", then:

  1. Message, "Are you sure?"
  2. Delete the date for "processed"
  3. Reinstate the "requested" status (which must exist, with a date)

Then, what to do if the "requested" status is deleted? The whole thing is a lot more complex than the simple date stamping you have for now. On the other hand, the format of your existing code already pushes the limits of its potential performance. If you want to expand your demands on the code you must give it a shape that can handle expansion. Today I've done that for you but now it's yours to maintain.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 005

    Dim C As Long

    ' Try not to interrupt the flow of the code
    ' either by GoTo or Exit commands
    With Target
        If .CountLarge = 1 Then
            If Not Intersect(Target, Range("BK5:BM500")) Is Nothing Then
                On Error Resume Next
                C = Val(Application.VLookup(.Value, Sheets("Key").Range("B2:C20"), 2, False))
                If C Then Cells(.Row, "AV").Offset(0, C).Value = Date
            End If
        End If
    End With
End Sub


What you said is absolutely right Variatus and the code that you have provided is another approach which I had'nt gave a thought.Just wow!

Im just giving a try to make you understand that as you said  "The user selects an action and a date gets written into the appropriate column. Then the user selects another action and another date gets written to another columnis right!

My concern is, If I by mistake enter a wrong option(say from a cell in BK) and then selects the right one(same cell in BK), there will be date stamp in both the columns corresponding the option which is not feasible. I want to trigger an event like, If I delete(through keyboard/by right click->clear contents) the former option from BK then it must clear the corresponding row's date stamp as well.
Is this possible?  
Dr Liss (rep: 12) May 13, '20 at 4:05 am
It would be very hard to take different action depending upon how a deletion was done. Did you fully consider my suggestion to first introduce a required sequence? Meaning if the user clicks on "process" before "request" the actions is rejected. If you have such a rule in place you could also reject a deletion of "request" while "process" is in place. And that, only finally, would open the door to deleting "process" if it was the last action taken.
Variatus (rep: 4402) May 13, '20 at 4:34 am
So just check if the cell is empty and then make sure that the corresponding time stamp cell is also empty - isn't that just a small IF statement check on the cell that was changed and then an offset of it to clear the correct cell? I ask because maybe I am getting something wrong.
don (rep: 1959) May 13, '20 at 4:34 am
Yes. On the ground it would be a "small IF statement". But it would need to be repeated in a loop and introduce the concept of entry rejection which you don't now have. I picked on the Image process because it's short and clearly sorted. I don't know if your other 2 columns allow for a similarly fixed sequence. The idea might become impossible if your process allows omission of steps. Even if all is already compliant with the notion or can be made so implementation would increase the volume of your code at least 4-fold.
Variatus (rep: 4402) May 13, '20 at 4:42 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login