Solved: Who opened my workbook?

[sukumar.vb]

Hi,

I am looking for best VBA program, if you could help me.

Step 1: User opens my workbook. VBA gets username from System.

Step 2: VBA unhides a sheet and in A2 writes Date, in B2 writes Time and in C2 writes username from system.

Step 3: Hides sheet again. (xlveryhidden)

Step 4: If Step 1 is repeated, then VBA writes these data in next record (row). It should not overwrite previous information.

[Kenneth Hobs]

Sounds like you want to audit use. Name a sheet Log and right click a sheet's tab and paste.

[VBA]'VOG II,http://www.mrexcel.com/forum/showthr...61#post1666961
Private Sub Worksheet_Change(ByVal Target As Range)
Dim NR As Long, tc As Integer, rTC As Range
Dim cell As Range, rLogCells As Range

Set rLogCells = Intersect(Target, Union(Range("A1:A10"), Range("D110"), Range("H1:H10")))
If rLogCells Is Nothing Then Exit Sub

On Error GoTo EndNow
'Speed routines, 'http://vbaexpress.com/kb/getarticle.php?kb_id=1035
SpeedOn
With Sheets("Log")
.Unprotect Password:="xyz"

For Each cell In rLogCells
tc = cell.Column
NR = .Cells(Rows.Count, tc).End(xlUp).Row + 1
Set rTC = .Cells(NR, tc)
rTC.Value = cell.Address(False, False)
rTC.Offset(0, 1).Value = Now
rTC.Offset(0, 2).Value = Environ("username")
'rTC.Offset(0, 3).Value = cell.Value
'rTC.Offset(0, 3).NumberFormat = cell.NumberFormat
Next cell

.UsedRange.EntireColumn.AutoFit
.Protect Password:="xyz"
End With
EndNow:
SpeedOff
End Sub[/VBA]

[sukumar.vb]

Hi, I thank for your interest and I found Step 1 was missed out. I am requesting to please review, if possible, could all 4 steps be met? We will prefer this sheet to be hidden. Password Protection can help manipulation.

[Kenneth Hobs]

Please don't make big quotes. I have never seen a case where they are needed.

The code that I posted does what you want and then some. Not all of your steps are really needed. For one thing, when you add time as I did, the date is added as well. Here is an example using the same concepts from the first reply but tailored more to your request. I also added some subs to make it easy to protect and unprotect the log. I added one more field to show open or close.

First, open VBE and set the name of a sheet to LogSheet. This is the codename, not the sheet name shown on the tab. Select View > Project Explorer, select a sheet object, and View > Properties Windows, to set the name property.

In a Module, change the value of pw to suit:
[VBA]Option Explicit

Const pw As String = "ken"

Sub Log(openClose As String)
With LogSheet.Range("A" & Rows.Count).End(xlUp).Offset(1)
.Value = Date
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = Environ("username")
.Offset(0, 3).Value = openClose
End With
End Sub

Sub LogProtect()
With LogSheet
.Protect pw, UserInterfaceOnly:=True
.Visible = xlSheetVeryHidden
End With
End Sub

Sub LogUnprotectOpen()
With LogSheet
.Unprotect pw
.Visible = xlSheetVisible
.Activate
.UsedRange.EntireColumn.AutoFit
End With
End Sub[/VBA]

In ThisWorkbook object:
[VBA]Option Explicit

Private Sub Workbook_Open()
LogProtect
Log "Open"
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Log "Close"
ThisWorkbook.Save
End Sub[/VBA]

[sukumar.vb]

I must admire and appreciate that design of program was excellent, except:


There was an error:

With Object, must be user defined type. I think correct VBA Program will be this, if we rename Sheet name to LogSheet:-

[vba]

' to be inserted into ThisWorkbook
Option Explicit
Const pw As String = "ken"

Private Sub Workbook_Open()
With Sheets("LogSheet")
.Unprotect pw
.Visible = xlSheetVisible
.Activate
.UsedRange.EntireColumn.AutoFit
End With
Log "Open"
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Log "Close"
With Sheets("LogSheet")
.Protect pw, UserInterfaceOnly:=True
.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Save
End Sub

[/vba]

[vba]

' to be inserted into Module1

Option Explicit



Sub Log(openClose As String)
With Sheets("LogSheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
.Value = Date
.Offset(0, 1).Value = Now
.Offset(0, 2).Value = Environ("username")
.Offset(0, 3).Value = openClose
End With
End Sub
[/vba]

[sukumar.vb]

ThisWorkbook program worked better:-


[vba]
' to be inserted into ThisWorkbook
Option Explicit
Const pw As String = "ken"
Private Sub Workbook_Open()
With Sheets("LogSheet")
.Unprotect pw
.Visible = xlSheetVisible
.Activate
.UsedRange.EntireColumn.AutoFit
End With
Log "Open"
With Sheets("LogSheet")
.Protect pw
.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Save
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With Sheets("LogSheet")
.Unprotect pw
.Visible = xlSheetVisible
.Activate
.UsedRange.EntireColumn.AutoFit
End With
Log "Close"
With Sheets("LogSheet")
.Protect pw
.Visible = xlSheetVeryHidden
End With
ThisWorkbook.Save
End Sub

[/vba]

[sukumar.vb]

Refer attachment for Solution.

I was not able to mark this thread as "Solved".

Thanks many to Ken