Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    RunTime Error 1004 (XL 2000 SP3)

    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("A6553 6").End(xlUp).Offset(0, 2).Value = Time$ & Space(5) & Date$
    ActiveWorkbook.Worksheets("UsageLog").Range("A6553 6").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

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    I suspect ActiveWorkbook isn't what you think it is. Change it to Workbooks("BookNameWithLog.xls")
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    Jan

    Thanks for your reply.

    I do not think that is the problem as I tried what you suggested and the error persists. I also made a copy of SaveWithoutSaver, stored it in the test book and used that macro to save so I am not changing workbooks at any stage (my original SaveWithoutSaver was in Personal.xls) and that did not get rid of the error either

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    Jan

    It did not work when I first made the changes but now it seems to be doing so. I will experiment further.

    I can see a further problem though, this is to be a template and if I use Workbooks("UsageLogTest.XLS") in writing in the time saved etc, this presumably will not work when the workbook is saved with a new name.

    Many thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    You could use ThisWorkbook; this will always be the workbook that contains the code, even if another workbook is active at the time.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    Exactly what I was going to say.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Adelaide, South Australia, Australia
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: RunTime Error 1004 (XL 2000 SP3)

    Thanks to you both

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •