Populating cells with current time from dropdown menu

[calvin771]

I have a shared workbook that I'm modernizing.

Currently, we track inbound jobs through to completion. They have three statuses: Not Started, Working and Completed.

I would like to automatically generate timestamps for the two statuses: Working and Completed.

I have a Status column "I" with a dropdown menu of the three choices. In column J I want the current time to populate when "Working" is selected. When "Completed" is selected, I want the current time to populate to column K without the time in column J changing.

I tried using a combination of an IF statement and the NOW() function with no success. NOW() refreshes each time the workbook is opened.

Can someone please point me in the right direction? I'm new to VB Scripting but think the answer lies there.

Thanks in advance!

 

[hiker95]

calvin771,

As you make choices in column I, columns J and K will be updated, and will not change unless you make a different selection.

Excel Workbook
	I	J	K
1	Not Started
2
3	Working	22:05:26
4
5	Not Started
6
7	Completed	22:06:05	22:06:16
8
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Right click the sheet tab you want the code in, and click on View Code. Paste the below code there (on the right pane) by pressing the keys CTRL + V
4. Press the keys ALT+Q to exit the Editor, and return to Excel

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Value = "Working" Then
  With Target.Offset(, 1)
    .Value = Now
    .NumberFormat = "hh:mm:ss"
  End With
ElseIf Target.Value = "Completed" Then
  With Target.Offset(, 2)
    .Value = Now
    .NumberFormat = "hh:mm:ss"
  End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

Then make changes in column I.

 

[calvin771]

That is the perfect solution.

Thank you very much!

 

[calvin771]

I protected the sheet to hide a formula in column B. When I chose an option from the dropdown menu in column I, a vb error came up and it highlighted the code ".NumberFormat = "m/dd hh:mm"" that I added to handle a third condition I wanted a timestamp for.

Is there a problem with the format of the NOW() function for the third condition? When it errored out, did it toggle the functionality off so that it stops working altogether? If so, how do I get it going again?

Any help is much appreciated!

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("I:I")) Is Nothing Then Exit Sub
If Target.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Value = "Working" Then
  With Target.Offset(, 1)
    .Value = Now
    .NumberFormat = "hh:mm"
  End With
ElseIf Target.Value = "Completed" Then
  With Target.Offset(, 2)
    .Value = Now
    .NumberFormat = "hh:mm"
  End With
ElseIf Target.Value = "Not Started" Then
  With Target.Offset(, -2)
    .Value = Now
    .NumberFormat = "m/dd hh:mm"
  End With
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[calvin771]

Further information regarding what I'm dealing with

The error that I recieve when the worksheet is protected is:

Run-time error '1004':

Unable to set the NumberFormat property of the Range class


I am able to click on End, Debug and Help.

When clicking on the Debug button, the following line is highlighted in the VB Editor:

    .NumberFormat = "M/DD hh:mm"

When clicking the End button, I am returned to the spreadsheet and the functionality tied to the dropdown menu in the I column no longer works.

 

[hiker95]

calvin771,

I protected the sheet to hide a formula in column B. When I chose an option from the dropdown menu in column I, a vb error came up and it highlighted the code ".NumberFormat = "m/dd hh:mm"" that I added to handle a third condition I wanted a timestamp for.

Try not protecting columns I, J and K.

 

[calvin771]

That didn't work.

It seems that I can't protect the sheet or workbook as it breaks the functionality that the VB Code added.

 

[calvin771]

I found the answer in a different thread. The code posted below solved the issue.

Sub MacroName()
Sheets("SheetName").Unprotect "PasswordHere"
 
'Rest of your code goes here
 
Sheets("SheetName").Protect "PasswordHere"
End Sub

A macro can't modify a protected sheet. The code above is a workaround. Curious eyes can see the password, but my average user won't even know how to look at the code.

 

[calvin771]

Yet more devastating news...

Due to the fact that the workbook I am using is shared, I am unable to protect the worksheet to hide the formulas.

Oh well! I've learned quite a bit revamping this spreadsheet. Thanks for all of your help!