How do I create a logfile

[Jan Grinwis]

Hi,

I have a shared excelfile with is edited by sometimes 8 users. I want
to create a logfile with the following items:
Date/time, sheetname, User, Cell, Old value, New value. I only want
the log the manually input.

I didn't start yet, I first want to figure out a way to make it.

I do have a login_username function.

I thought of:
- create/open a shared logfile
- use a worksheet_change function ???
- find out the user, cell, new value, sheetname and date/time

BUT:::::

- How can I find old_value
- How do I find the new line in the logfile
- What about multiple acces at the same time in the logfile


any suggestion??
is there a better/easyer way??

Jan

 

[bigwheel]

Have you checked out the Track Changes option? (Tools, Track Changes)

 

[Tom Ogilvy]

Look at the history option under shared files.

 

[Jan Grinwis]

Look at the history option under shared files.

Thanks Tom,

I found what I needed.
It now looks like this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim old_Val As Variant
Dim new_Val As Variant
Dim Sheet_Name As Variant

If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
new_Val = Target.Value
Sheet_Name = ActiveSheet.Name
Application.Undo
old_Val = Target.Value
Target.Value = new_Val

Open "C:\logfile.txt" For Append As #1
Print #1, Now, ";", Sheet_Name, ";", Target.Address, ";",
fOSUserName(), ";", old_Val, ";", new_Val
Close #1

ErrHandler:
Application.EnableEvents = True
End Sub

Can someone tell me what will happen when the logfile is accessed by
more than one person at the same time..By the way, the logfile is on a
netwerkdrive (not c

Regards Jan

 

[Dave Peterson]

I think it depends on what program accessed the file.

Some programs will lock the file and your code would error on the "open" line.

Some programs won't lock the file (essentially opening it in Readonly mode) and
nothing bad will happen.

If you know what program you'll use, use it to open the file. And make a few
changes in excel to see what happens.

I like a program called UltraEdit32 (www.ultraedit.com). It'll open text files
and even tell me when some other program has changed them and offer to reload.

===
But in general, copy that log file to a different name and open the copy.