Over twenty posts, so you should have read the Forum Rules and know how to use Code Tags
Macro to insert date stamp based on corresponding row containing drop down list
- CapG
- Thread is marked as Resolved.
-
-
-
Apologies!
I know now and will create a new thread with code tags.
-
I've added the code tags, so that is not necessary
-
Maybe this. It checks if a DateStamp exists and gives the user the chance to change it.
Code
Display MorePrivate Sub Worksheet_Change(ByVal Target As Range) Dim rCl As Range Dim x As Long, c As Range If Target.CountLarge > 1 Then Exit Sub If Intersect(Target, Range("BK5:BM500")) Is Nothing Then Exit Sub If Target.Value = "" Then Exit Sub On Error Resume Next x = Application.Index(Sheets("Key").Range("C1:C20"), Application.Match(Target.Value, Sheets("Key").Range("B1:B20"), 0)) If x >= 1 Then Set rCl = Cells(Target.Row, x + 48) If IsEmpty(rCl) Then rCl.Value = Date ElseIf IsDate(rCl) Then Select Case MsgBox("the cell already contains a date stamp, would you like to replace it?", vbYesNo Or vbQuestion Or vbDefaultButton1, "Add New Date Stamp?") Case vbYes rCl.Value = Date Case vbNo End Select End If End Sub -
I tried but no luck. If I delete(through keyboard or right clicking) the dropdown option selected, thhat should trigger to delete the corresponding row's date stamp.
Here is the sample file.
-
You said you wanted to incorporate in the existing code, which is what I did.
You would need a different event, Double_Click would be the best in my opinion. Check the code below, I think it is clearing the correct cell
-
Anything is convenient modify same code or adding a new code. I tried to run, its not working and I dont understand how beforedoubleclick is suitable for my requirement.
I want to capture delete event and auto populate that deletion in the datestamp cells.
-
There is no delete event to capture
-
Can you supply a workbook and more details on what you mean.
-
There is a file attached that has drop down menu in BK,BL,BM and the time stamp is auto populating to the same corresponding rows matching those 3 columns.Columns AW4-BJ4 are labeled with headings that correspond with the names in the drop down list.
So If I by mistake enters a wrong option(say from a cell in BK) and then selects the right one(same column BK), there will be date stamp in both the columns corresponding the option which is not feasible. I want to trigger an event, If I delete the former option from BK it must clear the corresponding row's date stamp as well.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!