Text and Date in a macro
Posted by Rich Blessington on February 16, 2001 5:05 PM
I have to "sign" about 40 timesheets each week. I wrote a macro that goes to cell x60, unprotects the worksheet, enters my initials and the the date stamp (ctrl + ;) then reprotects and saves the sheet. The macro records the date stamp as the date the macro was written and never updates it after that. If I use the TODAY() command, the date updates everytime the spreadsheet is opened. Is there a way to put my initials and the date stamo in a macro so that it enters the current date everytime I use the acro but doesn't update each time the spreadhseet is opened? All of the info has to go in one cell.
Here's the macro I tried:
Timesheet Macro
' Macro recorded 02/16/2001 by Richard Blessington
'
' Keyboard Shortcut: Ctrl+b
'
Application.Goto Reference:="R60C24"
ActiveSheet.Unprotect
ActiveCell.FormulaR1C1 = "rb 02/16/2001"
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Save
End Sub
Posted by Chris on February 16, 2001 7:49 PM
Rich,
Try this ... the only thing I'm not sure about is this produced current date at the time the macro is run but the format includes the time as well ....not sure how to format time out or whether that matters to you. I don't have the time right now to play with the date/time format issue.
What you need to do is declare the variable .... set the variable to the date ... such that the macro inserts "text" as opposed to a formula for date. The formula in the cell would update everytime the workbook is opened. Using the variable should work ok for you.
Posted by Dave Hawley on February 17, 2001 1:08 AM
Keyboard Shortcut: Ctrl+b
Hi Richard, try this
ActiveSheet.Unprotect
Range("X60") = "rb " & Date
ActiveSheet.Protect
ActiveWorkbook.Save
Dave
OzGrid Business Applications
