joieroxx
04-14-2008, 02:28 PM
I have a workbook that is accessed by several users on ONE stand-alone computer. I need a way to track changes made on this book - basically the who, what, when, how it was changed. The sheets are hidden unless a valid username and password are entered on my "main" sheet which is of course visible. I am using the following macro to achieve this:
Private Sub CommandButton2_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.Username.Value
strPword = Me.Password.Value
Select Case strUser
Case "user1"
If strPword = "password1" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user2"
If strPword = "password2" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user3"
If strPword = "password3" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly, "Timewriting"
End Select
End Sub
Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You must enter your user name & password to access sheet", vbCritical + vbOKOnly, "Password required"
End If
End Sub
-------------
Ok so that works, but now...how can I get a log of that user who last accessed the workbook with the date and time and what changes were made??? I'd like for these changes to be automatically recorded on an "very hidden" sheet everytime it is saved. Is this at all possible?
Suggestions are welcome. I know this would be easier with MS Access, but my colleagues are more comfortable with Excel; they find it easier.
Thank you!!
JM
Aussiebear: Edited to enable the code to be placed within the vba tags
Private Sub CommandButton2_Click()
Dim strUser As String, strPword As String, strWs As String
strUser = Me.Username.Value
strPword = Me.Password.Value
Select Case strUser
Case "user1"
If strPword = "password1" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user2"
If strPword = "password2" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case "user3"
If strPword = "password3" Then
Sheets(1).Visible = xlSheetVisible
Sheets(2).Visible = xlSheetVisible
Sheets(3).Visible = xlSheetVisible
Sheets(4).Visible = xlSheetVisible
Sheets(5).Visible = xlSheetVisible
Unload Me
End If
Case Else
MsgBox "Incorrect passwword or user name", vbCritical + vbOKOnly, "Timewriting"
End Select
End Sub
Private Sub CommandButton1_Click()
Unload Me
ActiveWorkbook.Close
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "You must enter your user name & password to access sheet", vbCritical + vbOKOnly, "Password required"
End If
End Sub
-------------
Ok so that works, but now...how can I get a log of that user who last accessed the workbook with the date and time and what changes were made??? I'd like for these changes to be automatically recorded on an "very hidden" sheet everytime it is saved. Is this at all possible?
Suggestions are welcome. I know this would be easier with MS Access, but my colleagues are more comfortable with Excel; they find it easier.
Thank you!!
JM
Aussiebear: Edited to enable the code to be placed within the vba tags