userPH
03-22-2013, 08:32 AM
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
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