Unprotect Sheet / Enter Time Stamp / Protect Sheet in Macro

[dshadel]

I am trying to make a spread sheet to record start/stop times. I do not want my users to be able to do anything except click a button that activates the macro to do the time stamp.

If a make seperate macros to unprotect the worksheet, do the time stamp, and then protect the worksheet again, it works fine.


Sub Unprotect()
'
ActiveSheet.Unprotect Password:="123"
End Sub


Sub Time()
'
ActiveCell.Application.SendKeys ("^; ^+;~"), True
End Sub


Sub Protect()
'
ActiveSheet.Protect Password:="123"
End Sub


If I combine the commands into one macro, it doesn't work.


Sub StampTime()
'
ActiveSheet.Unprotect Password:="426"
ActiveCell.Application.SendKeys ("^; ^+;~"), True
ActiveSheet.Protect Password:="426"
End Sub


I'm not sure what else to try. I don't do much work in vb so hopefully I'm just missing something simple.

 

[brian.wethington]

I sometimes have issues with sendkeys perhaps this will help.

Activesheet.Unprotect "426"
ActiveCell.Value = Now
Activesheet.Protect "426"

 

[dshadel]

I need the time entered to be static so it can be logged. I don't think I can accomplish this with the now function.

I think you're right about the SendKeys being the problem, though...

 

[Datsmart]

The Function "NOW" in a formula is not static like you say, but when used in a code, the result will remain static until the code is run again.

 

[brian.wethington]

This would not be static

ActiveCell.formula = "=Now()"

but this is slightly different

ActiveCell.Value = Now

Edit - BTW, This would have the same effect as the ActiveCell.Value = Now approach

ActiveCell.Formula = "=Now()"
ActiveCell.Copy
ActiveCell.PasteSpecial xlPasteValues

 

[dshadel]

Thanks guys!