Run-time error 1004

[Jat999]

Hi all


I have a workbook, where when opened, I need to log the user, date and time of opening, and then save the event.
The event is written to a sheet hidden from the user named "User_Data"
Simple 2 lines of code to achieve this:-

[FONT=Calibri][COLOR=#000000]Sub Workbook_Open()

Sheets("User_Date").Range("A" &Rows.Count).End(xlUp).Offset(1).Value = Application.UserName & "" & Now()
[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]ThisWorkbook.Save

End Sub
[/COLOR][/FONT]

When I run this, I am getting the Run-time 1004 error.

As it is an open workbook event, I am unable to test without reopening the book, so i took the code out and placed it into a seperate sub and called the seperate sub from the Workbook open event.
Still it falls over on initialisation, yet i can manually step through the Sub Id_Date code a second time around sucessfully?

[FONT=Calibri][COLOR=#000000]Sub Workbook_Open()[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]Call Id_Date[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]End Sub[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]_________________________________________________________________________________[/COLOR][/FONT]
[FONT=Calibri][COLOR=#000000]
Sub Id_Date()[/COLOR][/FONT]

[FONT=Calibri][COLOR=#000000]Sheets("User_Date").Range("A" &Rows.Count).End(xlUp).Offset(1).Value = Application.UserName & "" & Now()[/COLOR][/FONT]

[FONT=Calibri][COLOR=#000000]ThisWorkbook.Save[/COLOR][/FONT]

[FONT=Calibri][COLOR=#000000]End Sub[/COLOR][/FONT]

What am I doing wrong?

Thanks in advance

John
​

 

[Norie]

John

What's the error message?

PS You can manually run the Open event, just goto the ThisWorkbook module put the cursor in the sub and hit F5.

PPS That might not help finding out what the problem is because you probably won't get an error when you run it manually..

 

[Jat999]

Hi Norie

Method 'Rows' of object '_Global' Failed

One other point to note. The workbook always opens in protected mode, the user has to "Enable editing", however i do not believe this has any impact as the code fails after the open event.

Thanks for your quick response

 

[wideboydixon]

Is it this?

https://support.microsoft.com/en-us...il-from-workbookopen-event-when-exiting-prote

WBD

 

[wideboydixon]

I think that's the problem. You may need to defer the writing of the log to the Activate event. Something like this:

Private userLogWritten As Boolean
Private Sub Workbook_Open()

userLogWritten = False

End Sub
Private Sub Workbook_Activate()

If userLogWritten Then Exit Sub

Sheets("User_Date").Range("A" & Sheets("User_Date").Rows.Count).End(xlUp).Offset(1).Value = Application.UserName & "" & Now()
ThisWorkbook.Save
userLogWritten = True

End Sub

WBD

 

[Jat999]

Hi WED

Thanks- yes, this did solve it, (Good old MS undocumented feature), however the problem this now gives me is that everytime the user switches to another workbook, when they change back, the event is triggered again.

Whilst the write to the next free cell should not be an issue to the user in terms of speed, i probably need to disable the save event and maybe a trigger a save on close event.

 

[Jat999]

Hi WED

Nice one - You've cracked it for me.

Many thanks

 

[wideboydixon]

You're welcome Jet

WBD

 

[Jat999]

Sorry WBD - realised after I sent the mails