I am working on a workbook with a UsageLog sheet. The following writes the time and username when the workbook is saved. It works all right except that the lines:
ActiveWorkbook.Worksheets(“UsageLog”).Unprotect
and
ActiveWorkbook.Worksheets(“UsageLog”).Protect
Do not seemed to have any effect; if the sheet is unprotected to start with the macro runs properly but the sheet is still unprotected when the macro ends; if the sheet is protected before running the macro it gives an error 1004 at the line
ThisWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 3).Value = Time$ & Space(5) & Date$
Or, if this line is rem’ed out, 2 lines later (the second msgbox shows that ThisWorkbook and ActiveWorkbook are the same, and the macro falls over whichever is used).
Sub EnterSaveData()
On Error GoTo ErrorHandler
MsgBox (“In Macro EnterSaveData”)
‘following checks ThisWorkbook and ActiveWorkbook are UsageLogTest.xls
Dim name$, name2$
name$ = Application.ThisWorkbook.name
name2$ = Application.ActiveWorkbook.name
MsgBox “ThisWorkbook is ” & name$ & Chr(13) & Chr(13) & “ActiveWorkbook is ” & name2$
Application.ActiveWorkbook.Save
ActiveWorkbook.Worksheets(“UsageLog”).Unprotect
MsgBox (“In Macro EnterSaveData, have UnProtected Sheet”)
ThisWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 3).Value = Time$ & Space(5) & Date$
MsgBox (“Should have entered save date but not username”)
Application.ActiveWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 4).Value = Environ(“Username”)
MsgBox (“Should have entered save date and username”)
ActiveWorkbook.Worksheets(“UsageLog”).Protect
MsgBox (“In Macro EnterSaveData, have Protected Sheet”)
Exit Sub
ErrorHandler:
MsgBox (“error number”) & Err
Select Case Err
Case Is = 9
MsgBox (“Worksheet UsageLog is missing. Insert & name this sheet. Press OK to exit macro.”)
Exit Sub
Case Else
MsgBox (“Error other than UsageLog sheet missing. Error”) & Err
End Select
End Sub
Can anyone help? TIA
