Log of users who open the workbook

Hi y'all,
I used the search command and found several of your answers to this
question, but each of them failed in the VBA for some reason. Yes, I'm
in the VBA part (Alt+F11), I double clicked on ThisWorkbook, a window
opened and I pasted the given code, one at a time, into the window,
saved, exited the book, re-opened the book, and get an error each
time.

I dont really need to track changes, since I'll be the only one making
any changes. The other user(s) who open this are only there to get
information that I've entered, nothing else. I'd just like to know the
few times it was opened, who and when that was, so that I can format
the sheet accordingly.
The codes I've entered so far are as follows: 

Private Sub Workbook_Open()
Dim LastRow As Long
Set sht = Sheets("Audit")
LastRow = sht.Cells(Cells.Rows.Count, "A").End(xlUp).Row + 1
sht.Cells(LastRow, 1) = Environ("Username")
sht.Cells(LastRow, 2) = Now
End Sub
________________________________________________________________

Dim vOldVal 'Must be at top of module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bBold As Boolean

If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet1
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD
VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF
CHANGE")
End If

With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:= _
"OzGrid.com:" & Chr(10) & "" & Chr(10) &
_
"Bold values are the results of
formulas"
End If

.Value = Target
.Font.Bold = bBold
End With

.Offset(0, 3) = Time
.Offset(0, 4) = Date
End With

.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With

vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

On Error GoTo 0
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
vOldVal = Target
End Sub

Track/Report User Changes on all Worksheets in 1 Workbook 
The code below must be placed in the Private Module of the Workbook
(ThisWorkbook) you would like changes tracked and logged. To easily get
there right click on the excel icon, top left next to File and choose
View Code. In here paste the code below;

Dim vOldVal 'Must be at top of module
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
Dim bBold As Boolean
If Target.Cells.Count > 1 Then Exit Sub
On Error Resume Next
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If IsEmpty(vOldVal) Then vOldVal = "Empty Cell"
bBold = Target.HasFormula
With Sheet1
.Unprotect Password:="Secret"
If .Range("A1") = vbNullString Then
.Range("A1:E1") = Array("CELL CHANGED", "OLD
VALUE", _
"NEW VALUE", "TIME OF CHANGE", "DATE OF
CHANGE")
End If

With .Cells(.Rows.Count, 1).End(xlUp)(2, 1)
.Value = Target.Address
.Offset(0, 1) = vOldVal
With .Offset(0, 2)
If bBold = True Then
.ClearComments
.AddComment.Text Text:= _
"OzGrid.com:" & Chr(10) & "" & Chr(10) &
_
"Bold values are the results of
formulas"
End If

.Value = Target
.Font.Bold = bBold
End With

.Offset(0, 3) = Time
.Offset(0, 4) = Date
End With

.Cells.Columns.AutoFit
.Protect Password:="Secret"
End With

vOldVal = vbNullString
With Application
.ScreenUpdating = True
.EnableEvents = True
End With

On Error GoTo 0
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal
Target As Range)
vOldVal = Target
End Sub
_________________________________________________________________
Private Declare Function GetUserName Lib "advapi32.dll" Alias
"GetUserNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long
Private Declare Function GetComputerName Lib "kernel32" Alias
"GetComputerNameA" ( _
ByVal lpBuffer As String, _
nSize As Long) As Long

Private pAuditSheet As Worksheet
Private Const USERNAME_COL = 1
Private Const COMPUTERNAME_COL = 2
Private Const OPEN_TIME_COL = 3
Private Const CLOSE_TIME_COL = 4
Private Const OPEN_WB_NAME_COL = 5
Private Const CLOSE_WB_NAME_COL = 6
Private Const KEEP_ONLY_LAST_N_ENTRIES = 10

Private Sub Workbook_Open()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_Open
' Runs when the workbook is opened.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim RowNum As Long
Dim N As Long
Dim S As String

Application.ScreenUpdating = False
On Error Resume Next
Err.Clear
Set WS = Me.Worksheets("Audit")
If Err.Number = 9 Then
Set WS = Me.Worksheets.Add(before:=1)
WS.Name = "Audit"
End If
On Error GoTo 0
With WS
If .Cells(1, USERNAME_COL).Value = vbNullString Then
.Cells(1, USERNAME_COL).Value = "User Name"
.Cells(1, COMPUTERNAME_COL).Value = "Computer Name"
.Cells(1, OPEN_TIME_COL).Value = "Open Time"
.Cells(1, CLOSE_TIME_COL).Value = "Close Time"
.Cells(1, OPEN_WB_NAME_COL).Value = "Open WB Name"
.Cells(1, CLOSE_WB_NAME_COL).Value = "Close WB Name"
End If
.Visible = xlSheetVeryHidden
RowNum = .Cells(.Rows.Count, USERNAME_COL).End(xlUp)(2, 1).Row
N = 255
S = String(N, vbNullChar)
N = GetUserName(S, N)
.Cells(RowNum, USERNAME_COL).Value = TrimToNull(S)
N = 255
S = String(N, vbNullChar)
N = GetComputerName(S, N)
.Cells(RowNum, COMPUTERNAME_COL).Value = TrimToNull(S)
.Cells(RowNum, OPEN_TIME_COL).Value = Now
' Leave Close Time empty. It will be filled on close.
.Cells(RowNum, CLOSE_TIME_COL).Value = vbNullString
.Cells(RowNum, OPEN_WB_NAME_COL).Value = ThisWorkbook.FullName
' Leave Close Name empty. It will be filled on close.
.Cells(RowNum, CLOSE_WB_NAME_COL).Value = vbNullString
.UsedRange.Columns.AutoFit
End With
Application.ScreenUpdating = True
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Workbook_BeforeClose
' Runs when the workbook is closed.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim WS As Worksheet
Dim RowNum As Long
Dim EndRow As Long
Dim LastDel As Long
Dim FirstDel As Long

Application.ScreenUpdating = False
Set WS = Worksheets("Audit")
With WS
RowNum = .Cells(.Rows.Count, CLOSE_TIME_COL).End(xlUp).Row + 1
.Cells(RowNum, CLOSE_TIME_COL).Value = Now
.Cells(RowNum, CLOSE_WB_NAME_COL).Value =
ThisWorkbook.FullName
.UsedRange.Columns.AutoFit
If KEEP_ONLY_LAST_N_ENTRIES > 0 Then
EndRow = .Cells(.Rows.Count, USERNAME_COL).End(xlUp).Row
If EndRow > 2 Then
FirstDel = 2
LastDel = EndRow - KEEP_ONLY_LAST_N_ENTRIES
If LastDel > 2 Then
.Cells(FirstDel, "A").Resize(LastDel - 1,
1).Select
End If
End If
End If
End With

Application.ScreenUpdating = True
End Sub


Private Function TrimToNull(S As String) As String
'''''''''''''''''''''''''''''''''''''''''''''''''''
' TrimToNull
' Returns the portion of string S that is to the
' left of the vbNullChar, Chr(0).
'''''''''''''''''''''''''''''''''''''''''''''''''''
Dim N As Long
N = InStr(1, S, vbNullChar)
If N = 0 Then
TrimToNull = S
Else
TrimToNull = Left(S, N - 1)
End If
End Function
''''''''''''''''''''''''''''''''''''''''''
' END CODE
''''''''''''''''''''''''''''''''''''''''''

Any ideas?




-- 
txheart
0
txheart
4/21/2010 2:14:11 PM
excel.misc 78881 articles. 5 followers. Follow

0 Replies
736 Views

Similar Articles

[PageSpeed] 45

Reply:

Similar Artilces:

The database is in an unexpected state; Microsoft Access can't open it.
This morning I encountered an unusual and scary problem. While trying to open one of our databases I recieved this error. The database is in an unexpected state; Microsoft Access can't open it. This database has been converted from a prior version of Microsoft Access by using the DAO CompactDatabase method instead of the Convert Database command on the Tools menu (Database Utilities submenu). This has left the database in a partially converted state. If you have a copy of the database in its original format, use the Convert Database command on the Tools menu (Database Utilities submenu) ...

how to reference different workbooks/sheets in formula?
i have 30 different workbooks, each workbook has >20 individual sheets. i have a list with two columns, one is the name of the workbooks, and one is the name of each sheets. here is what i want to do. i want to do a count on 'column z' on every sheets. (column z on every sheets has n number of data, i want to count how many.) ie, column a column b column c (sheets) (workbooks) (number of data point) -------- ----------- ---------------------- g-1 a.xls =count('[a.xls]g-1'!$z:$z) g-2 a.xls =count('[a.xls]g-2'!$z:$z) g-3 a.x...

Trigger Macro on Open Spreadsheet
How can we force a macro to execute whenever a spreadsheet is first opened? Hi do you mean the file? If yes either name your macro Auto_Open() or put your code in the workbook event Workbook_Open() -- Regards Frank Kabel Frankfurt, Germany Beckie Davis wrote: > How can we force a macro to execute whenever a spreadsheet > is first opened? ...

How to delete user mailbox
Since I no longer have 5.5 installed (up'd to 2K) I had a friend ask how to delete a user mailbox and cannot for the life of me remember the interface for 5.5 and how to do that. Can someone give me the step by step to delete a mailbox cleanly? Thx select the mailbox in the Exchange Admin program and hit the delete key... "aaa" <anonymous@discussions.microsoft.com> wrote in message news:1380c01c44417$2d87fd40$a301280a@phx.gbl... > Since I no longer have 5.5 installed (up'd to 2K) I had a > friend ask how to delete a user mailbox and cannot for > the lif...

Check if Form is Open
I need to check to see if a form is open in the Onclose of another form. And if it is to requery it, if not do nothing. I did find a Function on The Access Web web site but I'm not sure how to utilize the function. Function fIsLoaded(ByVal strFormName As String) As Integer 'Returns a 0 if form is not open or a -1 if Open If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <> 0 Then If Forms(strFormName).CurrentView <> 0 Then fIsLoaded = True End If End If End Function I need to run this in the OnClose of a form to see if frmHome is o...

cash drawer not opening but reciept is printing
I have installed Microsoft RMS 2.0 on Windows Vista. I had issues installing my EPSON printer TM-T88IV. It would not work when i put it as "OPOS" device but it worked when i switched it to a "Windows" Device. Now the reciept prints but the problem is that the cash drawer wont open when the reciept is printed. The cash drawer is APG VB 320-BL1616. Any ideas whats wrong with it? thanks Bad cable perhaps? Can you switch cables with another register that IS working to see if it's the cable/pin configuration? Just a thought. "Akbar Mian" wrote: > I...

I don"t office,but need Excel or some part of it to open a web pag
help me I don't won't to load all of office "cowboy" wrote: > help me I don't won't to load all of office Hi you can buy Excel as separate product. -- Regards Frank Kabel Frankfurt, Germany "cowboy" <cowboy@discussions.microsoft.com> schrieb im Newsbeitrag news:5B015950-A7AC-49D0-9386-7EFFF9B9404D@microsoft.com... > help me I don't won't to load all of office Hi, When about to load office, choose custom. You can then choose when to load certain parts. You can install them when you use them the first time. The installer will ask ...

User Name prompts
Hi, I am using Microsoft Outlook 2002 and have a two word username separated by a space. I have been having this problem with Outlook wherein I have entered my username and password in the account setup process, but it still prompts me for it whenever it is checking for new messages. Also, when it prompts, it shows the username without the space. Does Outlook not accept usernames with space, because I tried changing the original settings many times, but every time I open the Edit window of email accounts, I see my user name without space. ...

Automatically display set text based on users composition
Hi, im trying to do something really simple, trouble is i dont know what the feature's called to be able to search for tips on how to do it. Basically in outlook messages, when a user begins writing a sentence e.g. "in the terms of" i need a tag to pop up that allows the user to press enter and then the remainder of what they will want to type in will be inserted in, its a yellow tag that comes up above the words. i dont know where it needs to be created and enabled. Cheers, Rhys. ...

Excel
Excel is the only application that I use in Windows that wipes out the clipboard after I paste. I often need to past the same information multiple times and I wishthere was an option box that allowed me to keep the clipboard after every past ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree&quo...

Anyone Else Use Database Query to Query Another Sheet in the Same Excel Workbook?
Wow! I tried this out, and it seems to work beautifully. It's odd, though, that I haven't seen this technique mentioned in any of the several Excel references that I've looked at. Basically, I have a workbook with several worksheets...one worksheet contains a large list with all the records. I wanted to set up the other worksheets to have certain functions...I wanted them to use only certain columns from the main list, and to contain only certain records from the main list that met specified conditions. This is the best solution I have found so far - i.e., creating database q...

Excel files cannot open.
my office version is office 2000 SR-1,when open it ,Only any of the files appears error message" Excel.exe has generated errors and will be closed by the windows.you will need to restart the rogram. An error log is being created."But these files is very important,please ask for help,thanks lot. "benson.chen" <anonymous@discussions.microsoft.com> wrote in news:052001c3d973$fffcf460$a401280a@phx.gbl: > my office version is office 2000 SR-1,when open it ,Only > any of the files appears error message" Excel.exe has > generated errors and will b...

Users not showing up??
I'm having trouble adding users and mailboxes in Exchange 2003. My Exchange 2003 box is connected to a Windows 2003 DC. On the Exchange box, whenever I add a user in the Active Directory, it shows up as an account, and all the Exchange tabs are assigned to the user....however under Exchange System Manager no mailbox is created...even though I've run Exchange Tasks in the AD on that same box. Is it because I have to somehow sync the AD on the Exchange box to the DC, or is it something else? Please help!! On Mon, 8 Aug 2005 15:42:02 -0700, "Scott S." <ScottS@discussio...

How do I open a .pub file in publisher?
A customer sent me a .pub file. I had 97 version to no avail. Bought 2003 to no avail. How can I open the unknown version in an application with internal inconsistancy. I believe the current version of Publisher, 2003 will open all previous versions of Publisher files. Publisher 2003 requires Windows 2000 or XP. -- Don Vancouver, USA "Oscar" <Oscar@discussions.microsoft.com> wrote in message news:A2829F96-69EC-4E36-92A4-AD2A552F8101@microsoft.com... >A customer sent me a .pub file. I had 97 version to no avail. Bought 2003 >to > no avail. How can I open the...

Excel: how to merge data from 2 workbooks with 1 col. in common?
I've got two separate workbooks. Each workbook contains unique columns except for one, which is a "key" column, or an id field. Example: I have a client # column in both workbooks. In the first workbook, I also have first name and last name columns. In the second column, I have phone number and address columns. How do I merge the two workbooks into one so that for each client # I have their first name, last name, phone number, and address. Well, I think I'd start by getting all the data into one workbook. With both workbooks open, select the worksheet with the phone n...

Suddenly not able to save new or open previous files with name lengths over 45 characters
I'm using Excel 2003 if it matters ... I'm wondering if there is a known bug/issue on this. For what ever reason, when I try to save a new file (or for that matter open something done previously) with a name that has more than 45 characters or so (might be off by a couple), I get message that it can't save and then gives me a bunch of reasons (file path might not exist, might have too many characters, folder might be read only, etc). I know file exists in the folder I'm checking - I can email myself the file and open from the email so its definitely there! Any sane insight? ...

This error in event log
Hi all I do have exch2003 and outlook 2003 I did get this error 4 times today in the event log. I cannot find concrete info on it on how to solve it. Should i ignore it or is it trying to tell me something important : Thanks for ll the help Error 0x6bb deleting unused restricted view from folder 1-D86610 on database "First Storage Group\Mailbox Store (<servername>)". Microsoft Exchange Information Store will try to delete the view again at the next maintenance interval. For more information, click http://www.microsoft.com/contentredirect.asp. http://support.microsof...

VSS and Snapshots/Clones log purging and mixing backup types
I would really appreciate if anyone could answer the following questions for me as we are looking into the use of iSCSI block storage and snapshot technology: 1. Is a snapshot capable of being rolled forward in the event of a DB failure/corruption to provide a no-loss restore in the event of DB corruption? 2. Can a snapshot be mounted into an RSG? 3. Can agent based conventional backups be mixed with VSS backups? 4. If snapshotting is used how are log files purged? 5. As snapshots are deltas are backed up clones required in order to protect from disk failures? The original data needs to b...

how do I set a semi-log plot on Excel
Create your chart. Double click on the vertical axis, and on the scale tab, check the Logarithmic Scale box. If you decide you want a log-log chart, you need to start with a scatter chart, In a scatter chart, both axes offer the Logarithmic Scale option. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ ...

Which 'event' to run pivot chart formatting code on file open?
Greetings I have code to change my pivot chart type to a mix of line and column also to refomat colours I need to run the code so that the user sees the changes when the file is opened Ive tried the 'workbook open' event and the chart activate event but noy joy so far. (Once the file is open, manualy triggering the code works perfectly) Any ideas? Thanks I don't know why workbook_open doesn't work. You could use Application.OnTime to run your code a short time (like 2 sec) after the workbook opens. - Jon ------- Jon Peltier, Peltier Technical Services, Inc. http://Peltier...

Non local users
Hi all Our company is going to use Exchange 2003 server as our primary mail server. People can send mails via it and they also can receive mails from exchange. Problem is that also non local users can send mails via our server. It is not nice if somebody is using your server as spam server. Could someone advise me how I can block non local users to send mails via our server? Thanks Topi Look at the following links, HOW TO: Prevent Unsolicited Commercial E-Mail in Exchange 2000 Server http://support.microsoft.com/default.aspx?scid=kb;EN-US;319356 HOW TO: Prevent Exchange 2000 from Being...

Multi User Application
Hi to all, I am new to access develop applications. I created application for multi user idea I want my application can be accessed by multi users. So I want to know how to distribute or let them access from my computer. I have static ip address 172.28.34.115 and created application in shared the folder. Users have access runtime 2007. Access 2007 used to develop 5 users to connect the database application Please guide me how multi users can access my application with all rights. They can do data entry as well as run query/report. -- Pankaj On Fri, 12 Feb 2010 02:53:01...

Multiple Users for office #2
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Hi, if I have office:2008 installed on my imac and I set up a new user (one of may staff has left and the new person is using the same machine) how do I get office to appear in the list of applications? I am hoping i dont have to reinstall given it is the same licence being used, just by a new person. All help much appreciated CSR > Hi, if I have office:2008 installed on my imac and I set up a new user (one of may staff has left and the new person is using the same machine) how do I get office to appear in the...

Money 2006 freeze on opening a file
I have been helping a friend of mine setup his new computer, and everything has gone fairly smoothly except for using Money. He used to use Money 2000 on a Windows 98 machine, and Money 2006 was purchased for use with the new Windows XP machine. We are running into problems periodically when opening a file. Sometimes when trying to open a file, when another is already open, Money will just freeze up. This does not happen all of the time, and it does not seem to happen with any certain file. I have searched around a bit on Microsoft's Knowledge Base but have not been able to find...

Can't open office documents from my emails until word is openned f
When I try to open a word document or excel spreadsheet that is saved as an attachement to an email, I get the message: Windows cannot find ‘c:/users/mona/appdata/local\microsoft\windows\temporary internet files\low\content.IE5\SB2EHHFL\xxx[1].xls\’ Make sure you typed the name correctly, and then try again. If I open Word or Excel before clicking on the attachement to open it, then it works. This problem only happens when using my laptop which is running Windows Vista and office 2007. I do not have any problems with this on our desktop which is running XP and office 2003. Wo...