VBA - Activeworkbook.save & Workbook_BeforeSave problems

[userPH]

So i have a workbook on a network. Multiple users access this workbook throughout the day. Occasionally when a user tries to access the workbook they get the message file is locked by 'another user' instead of displaying the users network id. I have looked into the various reasons why this happens and can not seem to pinpoint the exact reason for it in our case as it seems to happen sporadically. Instead i am attempting to write some code that will display the username of the person currently in the file when someone opens the file in a read only state. Below is what i have...

In Workbook
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim WS As Worksheet For Each
WS In Worksheets WS.Unprotect Password:="******"
If WS.FilterMode Then WS.ShowAllData
WS.Protect Password:="******", _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True, _
AllowFormattingRows:=True, _
AllowInsertingColumns:=True, _
AllowInsertingHyperlinks:=True, _
AllowDeletingColumns:=True, _
AllowSorting:=True, _
AllowFiltering:=True, _
AllowUsingPivotTables:=True
Next WS
End Sub


In Modules
Sub auto_open()
If ActiveWorkbook.ReadOnly = False Then
'Sheet1 renamed to List and sheet is hidden
list.Range("A2").Value = Environ("username")
End If
ActiveWorkbook.save
End Sub


Sub CurrentUser()
MsgBox ("Current User is " & list.Range("a2").Value)
End Sub

everything seems to work except it doesnt appear to be saving the workbook after it sets the value of A2. *note the lowercase s in "ActiveWorkbook.save" indicating improper syntax

IE: I open the sheet and save it but leave it open, someone else opens it and calls the macro, it displays my username. Sucess I close workbook someone else opens workbook but does not hit save. I then open the workbook while there still in it and call the macro. It displays my username. Fail

Please how to i get this to execute a save automatically after it changes the value of A2, also keep in mind i am very new to vba so assume i know nothing. Thanks

[SamT]

Open Event of Workbook Object
Private Sub Workbook_Open()

If ThisWorkbook.ReadOnly = False Then
'Sheet1 renamed to List and sheet is hidden
Sheets("list").Range("A2").Value = Environ("username")
End If

ThisWorkbook.Save

CurrentUser 'Calls Sub
End Sub

[userPH]

Thanks SamT. Code did not work though. As soon as i pasted your sub into the workbook it changed the Capital S in ThisWorkbook.Save to lowercase and does not appear to be saving on open. Its the same issue i was having with activeworkbook.save in a auto_open sub

[userPH]

This problem was answered at the following. Thanks


h x x p: // w w w*mrexcel*com/forum/excel-questions/692982-visual-basic-applications-activeworkbook-save-workbook_beforesave-problems. h/t/m/l