unprotecting a sheet ?

[mikeprice53]

Is there a way of using VBA to log whom unprotected the sheet ?

I am sure I have had similar code before but cant get anything to work ?

Thx

[mikeprice53]

Sorry forgot to mention that I will be using the

CuFN = Environ("Fullname")

statement to log there fullname off out network :0)

[DRJ]

What you could do is have a userform come up when they want to unprotect. i.e. they can push a button to unprotect. The user would not know the actual password to unprotect, but they would have their own username and password to input into the userform and the userform would unprotect the sheet and log the name and time somewhere.

[mikeprice53]

great thought but the actual need for this is to see who has access to our secret ( sort of ) password as our work is becoming changed when it shouldnt be with only 3 of us with password access...

So to be able to log them secretly this would be out of the question...

I wasnt sure that there was a good method to do this or not and presumabley not but thanx very much for your reply though anyhow :0)

[DRJ]

There is no before unprotect even, but you could be a code in the selection change event to see if the worksheet is protected. If not log the time and username.

[Aaron Blood]

What about this?

You could define the range of cells that is not allowed to change in VBA, perhaps call it ProtectedRange. Then maybe use the change event triggered to just those cells to record the evil doer's name to a text file or something like that.

You wouldn't be tracking who unprotected the sheet. But you'd be able to see who is changing cells that are s'posed to be locked.

Or... maybe just on the change event of the sheet, examine the cell. If the property is set to locked, record the evil doer and make note of the cell they changed. Sort of assumes the evil doer unprotects the sheet but doesn't bother to change the protect settings on the cells. Probably a safe assumption.

[Davers]

You could try this...create a sheet called "UserInfo". Put this bit o' code in a Module:

Option Explicit

Declare Function WNetGetUser Lib "mpr.dll" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Public Function UserId() As String

    Dim szName As String * 80
    Dim szUserName As String * 80
    Dim lpnBufferSize As Long
    Dim Status As Integer

    lpnBufferSize = 255
    Status = WNetGetUser("", szUserName, lpnBufferSize)
    If (Status = 3) Then
        UserId = "Unauthorized"
    Else
        'Return up to first Null.
        UserId = UCase(Left$(szUserName, InStr(szUserName, Chr(0)) - 1))
    End If

    Exit Function

End Function

and this bit o' code in the "ThisWorkbook" open event:

Private Sub Workbook_Open()
Application.ScreenUpdating = False
With Sheets("UserInfo")
    .Visible = True
    .Activate
End With
NextRow = _
    Application.WorksheetFunction.CountA(Range("A:A")) + 1
    Cells(NextRow, 1).FormulaR1C1 = "=UserId()"
    Cells(NextRow, 2).FormulaR1C1 = "=NOW()"
    Range("A:B").Copy
    Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    Range("A1").Select
Worksheets("UserInfo").Visible = xlVeryHidden
End Sub

And then lock your project...I tried it on my computer, and I couldn't tell it was logging me...dont' forget to lock your project so they cant see the code...you would also have to toss something in there so you could unhide the very hidden sheet for when you wanted to check it...

Hope this helps,

Dave

[Davers]

Well, after re-reading again...I guess my answer is not quite what you had in mind....although it will tell you who was in last...so if you know you were in and everything is fine...then someone else logged in and things are changed...well...it's sorta there...

Have a good day,

Dave

[Davers]

And a neat bit o' code taken from j-walk.com...

If you would like to keep track of who opens a workbook, and at what time, add the following procedure to the ThisWorkbook code module.

Private Sub Workbook_Open()
    Open ThisWorkbook.Path & "\usage.log" For Append As #1
    Print #1, Application.UserName, Now
    Close #1
End Sub

When this procedure is executed, it appends the user name and the current date/time to a text file. The file is stored in the workbook's directory, and is named usage.log. If the text file does not exist, it is created. You can, of course, change the code so the text file is written to a different directory...

Much much nicer than what I suggested!!!

Dave

[Aaron Blood]

I was thinking something along those lines...

But more like this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Locked Then
        TrackFile = "C:\Tracker.txt"
        TargUser = Application.UserName
        TargAddr = Target.Parent.Name & "!" & Target.Address(False, False)
        TargVal = Target.Resize(1, 1).Text
        TargDate = Format(Now, "yy/mm/dd hh:mm")
        x = TargDate & vbTab & TargUser & vbTab & TargAddr & vbTab & TargVal
        Open TrackFile For Append As #1
        Print #1, x
        Close #1
    End If
End Sub

This way it only tracks the changes of protected cells.