Macro that records the time it was used

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9
So here is the situation,

I have a macro for changing a cell's fill color when a button is clicked. I would like the macro to record the date/time it was used on each cell in a corresponding cell on a separate sheet. In other words, If the button is clicked, I want a time stamp in a cell on a different sheet. That cell must correspond with the cell modified by the macro. Below is the current VB code for the macro. Any help would be greatly appreciated.


Sub highlightgreen()
' highlights the current cell in green to denote passage through inspection device
' Keyboard Shortcut: Ctrl+g
ActiveSheet.Unprotect Password:="wouldntyouliketoknow"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Protect Password:="wouldntyouliketoknow"
End Sub

I thought of using a time stamp code similar to the one below (which I used in a different workbook). But I'm not sure how to implement it.

Sub timestart()
With ActiveCell
.Value = Time
ActiveCell.Offset(0, 1).Range("A1").Select
End With
End Sub
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Hi WarriorJ

You can do something like this in a regular Module.

Code:
Sub SetChangeTimeStamp(wsLogSheet As Worksheet)
    If Selection.Worksheet.Name <> wsLogSheet.Name Then
    Application.EnableEvents = False
    wsLogSheet.Range(Selection.Address).Value = Now()
    Application.EnableEvents = True
    End If
End Sub

Then, in your code module
Code:
Call SetChangeTimeStamp(ThisWorkbook.Sheets("Sheet4"))
Update "Sheet4" with whichever sheet should receive the timestamps.

HTH
 

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9
Ok, that looks promising. However, being fairly new to VB I'm not understanding how that should interact with my current code (or if it should). Could you provide some code comments to explain? Also, you mention a difference between a regular module and a code module. I guess I don't understand the difference. Could you define them? Thanks.
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Sure


Code:
Sub SetChangeTimeStamp(wsLogSheet As Worksheet)
    'wsLogSheet is a worksheet object identified/passed from the calling procedure [your macro]
    'It identifies, on which sheet, the timestamps should be recorded


    'Exclude the datestamp Sheet where a Selection may be there (avoids any recursive holes)
    If Selection.Worksheet.Name <> wsLogSheet.Name Then
    '[updating these cells would be considered an event; so as not to fire anything else ]
    Application.EnableEvents = False    'Turn off application events 
    
    'The Range here will be on the sheet you identify, represented by 'wsLogSheet'
    'The Range is updated en-mass, with the address provided by Selection, with the date/time stamp provided by Now() 
    wsLogSheet.Range(Selection.Address).Value = Now()
    
    Application.EnableEvents = True 'Turn on application events to resume normal operation


    End If
End Sub
Note!! that this works on Selection (as does your macro), So no matter what other sheet has focus, the Selection within that sheet has potential to update these d/t stamps!!


To call it from your macro


Code:
Sub highlightgreen()
' highlights the current cell in green to denote passage through inspection device
' Keyboard Shortcut: Ctrl+g
ActiveSheet.Unprotect Password:="wouldntyouliketoknow"
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 5296274
.TintAndShade = 0
.PatternTintAndShade = 0
End With
ActiveSheet.Protect Password:="wouldntyouliketoknow"


[B]Call SetChangeTimeStamp(ThisWorkbook.Sheets("Sheet4"))[/B]


End Sub

A 'regular' module, as created by right-click on ThisWorkbook and 'Insert Module'; as opposed to a Worksheet or the ThisWorkbook module itself (though should technically still work from there).
The same place your recorded macro resides would be named Module1, Module2, etc.
In there would be fine as well.



 

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9

ADVERTISEMENT

Thanks for the Help Tweedle!

I'm going to implement that code today and I'll let you know how it works.
 

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9
Tweedle,

The code worked great! Thanks for the help. I would like to use the code to do the same thing on several sheets within the same workbook. In other words. I want to be able to use the same macro on multiple sheets without having to create a separate instance for each sheet. Is that possible? Can I use some sort of relative referencing? I don't suppose it is really that hard to create multiple instances. It just seems cluttered. Thanks for your time!

WarriorJ
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559

ADVERTISEMENT

Are you doing some analysis or Conditional Formatting on these timestamps or just recording them as a log?
If they are just a log, then there are other options.

Recording them in a Journal-style, either in a single sheet within this workbook, in a separate workbook, or out to a txt/csv file; or could add/update a comment for each cell - there are many ways to keep a record of events.

FYI: The sheets recording the timestamps can be hidden.
 

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9
It is actually just a log. I have three sheets on which information will be entered. As the inspector checks the items he/she will use the macro to change the cell color based on the results. I would like to record the time each serialized item is inspected. There is a separate program that records photographs of the items and saves the files based on the date and time. I would like to have the inspection records so that I can corroborate data between the two sources. In other words, I would like to be able to identify what picture goes with each serialized item. If there is a simpler way to accomplish this I am all ears!

Below is some code that I tried which failed miserably. Would you mind pointing out my error?

Code:
Sub highlightgreen2()
' highlights the current cell in green to denote passage through inspection device
' Keyboard Shortcut: Ctrl+g
    If ActiveSheet("1st Shift") Then
        ActiveSheet.Unprotect Password:="noixa"
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ActiveSheet.Protect Password:="noixa"
        
        Call SetChangeTimeStamp(ThisWorkbook.Sheets("1st shift x-ray times"))
    
    ElseIf ActiveSheet("2nd Shift") Then
    ActiveSheet.Unprotect Password:="noixa"
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ActiveSheet.Protect Password:="noixa"

        Call SetChangTimeStamp(ThisWorkbook.Sheets("2nd shift x-ray times"))
    
        ElseIf ActieSheet("3rd Shift") Then
        ActiveSheet.Unprotect Password:="noixa"
        With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 5296274
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        ActiveSheet.Protect Password:="noixa"
        
        Call SetChangeTimeStamp(ThisWorkbook.Sheets("3rd shift x-ray times"))
        
    End If
End Sub


Thanks for all your help! :)
 

tweedle

Well-known Member
Joined
Aug 1, 2010
Messages
1,559
Failed Miserably? How so?

I see a type-oh:
ElseIf ActieSheet("3rd Shift")
ElseIf ActiveSheet("3rd Shift")
 

WarriorJ

New Member
Joined
Jan 6, 2014
Messages
9
Thanks, I did find that error and one other type-oh. Still getting an error.

RunTime Error '438':
Object doesn't support this property or method.

I'm trying to run the macro from an onscreen button.

Any ideas?
 

Forum statistics

Threads
1,136,354
Messages
5,675,292
Members
419,559
Latest member
BraytonM

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
Top