Run-time error 1004

Jat999

New Member
Joined
May 7, 2016
Messages
49
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:-

Code:
[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?


Code:
[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? :confused:

Thanks in advance

John
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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..:)
 
Upvote 0
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
 
Upvote 0
I think that's the problem. You may need to defer the writing of the log to the Activate event. Something like this:

Code:
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top