VBA: Log Users with Workbook_Open event

[jeran042]

I have a small piece of code that will log:

1. Application.UserName
2. Environ("username")
3. Format(Now, "yyyy-mm-dd hh:mm")

My question/problem is, I want to run this code, and then save the workbook. So this information will be saved even if the user closes workbook without saving. Here is what I have for code:

Private Sub Workbook_Open()



Dim LastRow As Long


LastRow = Cells(Rows.Count, 3).End(xlUp).Row + 1


Sheets("Sheet1").Cells(LastRow, "A").Value = Application.UserName
Sheets("Sheet1").Cells(LastRow, "B").Value = Environ("username")
Sheets("Sheet1").Cells(LastRow, "D").Value = Format(Now, "yyyy-mm-dd hh:mm")


ThisWorkbook.Save
App.DisplaAlerts = True






End Sub

However when I try to close the workbook, I am still being prompted to save changes, when the only change that was made was the logging of info. Can someone offer a solution to my problem?

Very much appreciated

 

[hotabae]

Well, mine seemed to work when I copy/pasted your code (after a couple revisions)

ThisWorkbook.Save
App[COLOR="#FF0000"]lication[/COLOR].Displa[COLOR="#FF0000"]y[/COLOR]Alerts = True

 

[MARK858]

@ jeran042, I can see you attempting to turn DisplayAlerts on but where do you turn them off?

 

[jeran042]

MARK858,
This was a poor piece of cutting and pasting on my part. I accidentally cut and pasted the code above from my "Work in Progress" module, and not the final I was testing.
I did have the

Application.DisplayAlerts = False

in the code I meant to paste. As well as the correct

[COLOR=#333333]App[/COLOR][COLOR=#FF0000]lication[/COLOR][COLOR=#333333].Displa[/COLOR][COLOR=#FF0000]y[/COLOR][COLOR=#333333]Alerts = True[/COLOR]

and it seems to work sometimes but not others?

Also, would anything in this code prevent it from writing to a hidden sheet?

 

[hotabae]

By the way,

It appears you are trying to find the last row with data to write a new log.

If that is the case you will want to change this line to search A,B, or D (instead of C)

LastRow = Cells(Rows.Count,[COLOR=#ff0000] 1[/COLOR]).End(xlUp).Row + 1

Edit: Unless you are just trying to place this single line after a worksheet full of data (which I realize now is probably the case)

 

[jeran042]

Scratch that question about writing to a hidden sheet, I believe I figured that part out.
Still unsure why this only works intermittently?

So my code now looks like:

Private Sub Workbook_Open()

Dim LastRow As Long


'Turn off Alerts
    Application.DisplayAlerts = False


'Find the first blank row
    LastRow = Cells(Rows.Count, 3).End(xlUp).Row + 1


'Unhide worksheet
    Sheets("Sheet1").Visible = True


'Capture the Username, Windows username and the date & time sheet was opened
    Sheets("Sheet1").Cells(LastRow, "A").Value = Application.UserName
    Sheets("Sheet1").Cells(LastRow, "B").Value = Environ("username")
    Sheets("Sheet1").Cells(LastRow, "C").Value = Format(Now, "yyyy-mm-dd hh:mm")


'Rehide worksheet
    Sheets("Sheet1").Visible = False


'Save workbook and turn alerts back on
    ThisWorkbook.Save
    Application.DisplayAlerts = True




End Sub

 

[MARK858]

Not sure exactly what you mean by

Still unsure why this only works intermittently?

as you haven't stated exactly what is/isn't currently happening so the biggest issue I can currently see is you haven't specified the sheet in

LastRow = Cells(Rows.Count, 3).End(xlUp).Row + 1

 

[jeran042]

Not stating which sheet to find the lastrow in may, in fact, be the problem.

By "Still unsure why this only works intermittently" I mean that sometimes it does not output correctly to "Sheet1". But now that I am thinking about it, on time when I opened the file with "Sheet1" being the activesheet, it worked. And with the other 2 sheets being completely blank, there would be no way to calculate the lastrow, so that is most likely why nothing (appears) to be happening

First thing in the morning, I will try specifying the which sheet I want to calculate the lastrow in.

Thank you for keeping in touch, this is very helpful for me,

 

[jeran042]

So to follow up, you were correct. The information was not being written to "Sheet1" because I did not specify which sheet to calculate the lastrow on.
Coincidentally, I was getting the desired result when I was unhiding, running the code and then hiding the sheet on open. But that doesn't seem like the the best way to go,

Thank you again for your help and for teaching me something,