Macros to triggger timestamp whenever one specific option is selected from the drop down list

Marta1

New Member
Joined
Jan 14, 2023
Messages
16
Office Version
  1. 2019
Platform
  1. Windows
Hi All,

Hope you are doing great

I need your help to create a Macros to trigger timestamp whenever one specific option is selected from the drop down list

I have a file called Employee(Copy) where is already a event macros to copy and paste the values in the another file. So, I wanted to add addition features to this sheet without disturbing the existed code but I am bit lost.

Whenever the status is changed to "In progress" then macros should add the current time stamp in the column "In progress start" and once the "In progress" status gets changed to another status then macros should create a time stamp in the column "In progress end"

Could you please help me with this? :)

Please have a look at the attached photos

Thank you in Advance

Regards
Marta
 

Attachments

  • Screenshot 2023-03-03 084026.png
    Screenshot 2023-03-03 084026.png
    71.8 KB · Views: 29
  • Screenshot 2023-03-03 084114.png
    Screenshot 2023-03-03 084114.png
    53.5 KB · Views: 29
Modified Version 2
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Event macro to be placed in your Source Workbook            '
' As soon as User fills in the Status in Column G             '
' Added CHRONOLOGICAL In Progress 1 and in Progres 2          '
' and your two conditions are fulfilled , New record is copied'
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Long, lastrow As Long, xcol As Integer
Dim wksh1 As Worksheet, wksh2 As Worksheet
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 7 Then Exit Sub
i = Target.Row
Set wksh1 = ThisWorkbook.Sheets("Sheet1")
' Adjust to your specific situation for Destination Names :
' Workbook Name AND Worksheet Name
Set wksh2 = Workbooks("Employee(final).xlsx").Sheets("Sheet1")

lastrow = wksh2.Cells(Rows.Count, "A").End(xlUp).Row + 1
    If Target.Offset(0, -4).Value >= 43586 Then
        If Target.Offset(0, -3).Value = "Charles" Or Target.Offset(0, -3).Value = "Nicolas" Then
            Select Case Target
                Case "In Progress"
                 ' Assumption : User will use the choice "In Progress" '''' Chronologically '''''
                 ' Use the First Empty Cell in Range I to L ( Columns 9-12 )
                 xcol = Range("I" & i & ":" & "L" & i).Find(What:="").Column
                 Target.Offset(0, xcol - 7) = Now
                Case "Completed"
                    ' All tests Passed - Copy Record and Add Flag next to copied record '''''''
                    Target.Offset(0, 1).Font.Name = "Wingdings"
                    Target.Offset(0, 1).Value = "ü"
                    wksh1.Range("A" & i & ":G" & i).Copy Destination:=wksh2.Range("A" & lastrow)
            End Select
        End If
    End If
End Sub
 
Upvote 1

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hi Marta,

Forgot to mention that, if you input data chronologically, you can have as many sub-levels (1,2,3,4,5,6,7, etc...) of your generic "In Progress" choice,
by simply extending the reference range beyond the L Column ... and adjust the macro accordingly ...
 
Upvote 1

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top