I am working on a worksheet template that incorporates a user log. The following macro is supposed to enter the time of saving and who saved in columns 3 and 4, across from the last entry in column 1 (which is the time opened). The macro works fine when stepped through or run in VBA, however when I try and save the workbook in XL I get a Runtime error 1004, this is at the line ActiveWorkbook…..
Sub EnterSaveData()
Application.EnableEvents = False
UnProtectUsageLog
On Error GoTo ErrorHandler
MsgBox (“In Macro EnterSaveData”)
ActiveWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 2).Value = Time$ & Space(5) & Date$
ActiveWorkbook.Worksheets(“UsageLog”).Range(“A65536”).End(xlUp).Offset(0, 3).Value = Environ(“Username”)
MsgBox (“Should have entered save date and username”)
ProtectUsageLog
Application.EnableEvents = True
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.”)
ProtectUsageLog
Application.EnableEvents = True
Exit Sub
Case Else
MsgBox (“Error other than UsageLog sheet missing. Error”) & Err
ProtectUsageLog
Application.EnableEvents = True
End Select
End Sub
Because I normally save via a macro that puts the path in the title bar, whilst looking at this problem I am trying to save via the following:
Sub SaveWithoutSaver()
‘Checks to see if the file has been saved by looking
‘for a colon in the FullName, which includes any path.
‘If InStr(1, ActiveWorkbook.FullName, “:”) > 0 Then
‘File has been saved. Show the Save dialog box and
‘put path in caption.
ActiveWorkbook.Save
‘ Application.ActiveWindow.Caption = ActiveWorkbook.FullName
‘ Else
‘file has not been saved. Show SaveAs dialog box and
‘put path in caption.
‘ Application.Dialogs(xlDialogSaveAs).Show
‘Application.ActiveWindow.Caption = ActiveWorkbook.FullName
‘End If
End Sub
Any help much appreciated
