Sleeper: Trace User changes/force save via VBA

[Immatoity]

Hi,

I am a newbie to VBA, and these forums are amazing.cheers
However being a "newbie" you will have to excuse my "simple question"?. I also cannot search the forums due to work firewall vagaries.

I have an excel file that approx 10-12 separate users need to go into, one after another , and make changes to a certain sheet. The supervisor of these users wants to be sure that once they open the file, they cannot change data,without being forced to save.
In other words, they must save the file if they change any data on the said sheet, and a record of who changed what and when is available
somewhere the supervisor can easily see who has done what, without him using VBA( he knows nothing VBA wise at all).
They used to print a report off and sign it manually, that way they had "proof" the individuals had done their bit -they want the same.

I have a list of the users, and their network logins, but don't really know where to start.

I was thinking of a more simple solution along the lines of seting up a worksheet as below, and once the user entered yes next to their name it saves the file.
However, my concern was that any other user could enter a yes against another users name, or delete others "yes" entries after, and no log?

USER
Joe Bloggs Yes
Ted Jones Yes


Any help is massively appreciated?thanks in advance you wizards!

[Killian]

Hi and welcome to VBAX

You can add some code to the Worksheet_change event of each sheet you want to monitor to achieve this

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox Now & ": Worksheet " & ActiveSheet.Name & " cell " & Target.Address _
        & " value changed to " & Target.Value & " by user " & Environ("USERNAME")
End Sub

As you can see, its just a message box but you can send this string to a log worksheet (that you can hide so the users can't edit it) or maybe output it to a text file.
If you add

ThisWorkbook.Save

to the event code, you can ensure each change is saved but if its a large file, this may become incredibly annoying. You might want to force the save on the WorkBook_Close event but also bear in mind that you'll be removing the option for someone to close without saving in the event the screw things up.

[OBP]

Have you looked at this Excel facaility?
View the History worksheet

1. On the Tools menu, point to Track Changes, and then click Highlight Changes. If the Track changes while editing check box is not selected, Microsoft Excel has not recorded any change history for the workbook.
   
2. Select the When check box and click All.
3. Clear the Who and Where boxes.
4. Select the List changes on a new sheet check box.
5. Click OK, and then click the filter arrows next to the column labels on the History worksheet to find the information you want.

Note Saving the workbook removes the History worksheet. To view the History worksheet after saving, redisplay it.

[Immatoity]

hi

cheers for that....how do I get it to send the string to the seperate file?? I would prefer it to go to a seperate excel workbook altogether so the supervisor can monitor it independently.

PS OBP..the list changes on a new sheet option is greyed out whatever I try!

[Killian]

Sorry, in a bit of a rush...
new worksheet change event

Private Sub Worksheet_Change(ByVal Target As Range)
WriteToLog Now, ThisWorkbook.Name, ActiveSheet.Name, Target.Address, CStr(Target.Value), Environ("USERNAME")
End Sub

code for a new module

'you'll need to change this path to your log file
Const strLogPath As String = "C:\Documents and Settings\Killian\Desktop\LOG.xls"
Sub WriteToLog(timestamp As Date, strWbName As String, strWsName As String, _
    strCellAddress As String, strNewValue As String, strUserName As String)
Dim wbLog As Workbook
Dim lngInsertPoint As Long
Application.ScreenUpdating = False
Set wbLog = Workbooks.Open(strLogPath)
    'find first free row
    lngInsertPoint = wbLog.Sheets(1).Columns(1).End(xlDown).Row + 1
    'add new data
    wbLog.Sheets(1).Cells(lngInsertPoint, 1).Value = timestamp
    wbLog.Sheets(1).Cells(lngInsertPoint, 2).Value = strWbName
    wbLog.Sheets(1).Cells(lngInsertPoint, 3).Value = strWsName
    wbLog.Sheets(1).Cells(lngInsertPoint, 4).Value = strCellAddress
    wbLog.Sheets(1).Cells(lngInsertPoint, 5).Value = strNewValue
    wbLog.Sheets(1).Cells(lngInsertPoint, 6).Value = strUserName
wbLog.Close True
    Set wbLog = Nothing
Application.ScreenUpdating = True
End Sub

[Immatoity]

hiya..from the question i am about to ask you will see how new I am!!

Whats the difference/benefits of the 2 above? Which is "best practice", or should I do both?

In the worksheet change thing above... I am stumped... lets say my file is C:\My Doc\test.xls, and I want the log to be C:\My Doc\log.xls, and the sheet is "abc", do I use the code???

Private Sub Worksheet_Change(ByVal Target As Range) 
    WriteToLog Now, C:\My Doc\test.xls.Name, abc.Name, C:\My Doc\log.xls.Address, CStr(Target.Value), Environ("USERNAME") 
End Sub 

as for the module......which bits do I substitute?

Sorry!

[Killian]

Ahh, sorry, no, I should explain... you need both bits of code
In your workbook "C:\My Doc\test.xls":
The worksheet_change code stays as last posted.
Then add a new module to the VBA project and add the second lot of code and change the line

Const strLogPath As String = "C:\Documents and Settings\Killian\Desktop\LOG.xls"

to

Const strLogPath As String = "C:\My Doc\log.xls"

and for a specific sheet in the log file ("abc"), replace the instances of

wbLog.Sheets(1)

with

wbLog.Sheets("abc")

Whats happening here is this:
When a cell value is changed, the worksheet_change event fires, which calls the WriteToLog routine and passes to it all those parameters that follow (the date/time, book and sheet name, cell address, new value and username) whatever they happen to be when the code is run.

The WriteToLog routine is expecting those parameters to arrive when its called

Sub WriteToLog(timestamp As Date, strWbName As String, strWsName As String, _ 
    strCellAddress As String, strNewValue As String, strUserName As String)

and then goes on to use the values its given.

Hopefully I clarified that...
I do have one other comment: its never a fantastic idea to put too much code in a worksheet_change event because the user can't do anything 'til its all finished running.
How much is too much? You'll know when you get it working - opening, amending and closing the Log workbook is quite clunky and if it isn't working out, it might be better to change the output to a text file, which will be much quicker.

[Immatoity]

thanks for your patience and understanding...will try it out

NB - just tried it and get a runtime error 1004 error.

Here is the code I have in the module

'you'll need to change this path to your log file
Const strLogPath As String = "C:\Documents and Settings\fitzpaja\My Documents\test2807.xls"
Sub WriteToLog(timestamp As Date, strWbName As String, strWsName As String, _
strCellAddress As String, strNewValue As String, strUserName As String)
Dim wbLog As Workbook
Dim lngInsertPoint As Long
Application.ScreenUpdating = False
Set wbLog = Workbooks.Open(strLogPath)
'find first free row
lngInsertPoint = wbLog.Sheets("abc").Columns(1).End(xlDown).Row + 1
'add new data
wbLog.Sheets("abc").Cells(lngInsertPoint, 1).Value = timestamp
wbLog.Sheets("abc").Cells(lngInsertPoint, 2).Value = strWbName
wbLog.Sheets("abc").Cells(lngInsertPoint, 3).Value = strWsName
wbLog.Sheets("abc").Cells(lngInsertPoint, 4).Value = strCellAddress
wbLog.Sheets("abc").Cells(lngInsertPoint, 5).Value = strNewValue
wbLog.Sheets("abc").Cells(lngInsertPoint, 6).Value = strUserName
wbLog.Close True
Set wbLog = Nothing
Application.ScreenUpdating = True
End Sub

I have created a blank excel file called " test2807.xls" in the directory above and renamed the only sheet in it to "abc".

The error highlights the line beginning wb.log...

also it nearly crashes my pc (512mb ram) - I think because the sheet that I want to log changes on is , pre any other user intervention, created from scratch every time (ie its data is wiped, and refreshed by a seperate macro) -usually about 1000 rows and 20 columns -and its going to try and log this every time.

Can I somehow get this "write log" function to only happen once I have refreshed the data, pre the 12 users changing it? They are on "thin-clients" and have not a lot of ram....

[splashscreen]

However, my concern was that any other user could enter a yes against another users name, or delete others "yes" entries after, and no log?

Hi,

I had a similar problem in that several user needed to mutate cells on the same worksheet, but werent supposed to be able to change each others data. This can be accomplished by setting different passwords on subranges of the sheet. see thread Allow users to edit range - problem

[Immatoity]

splashscreen....cheers for that, and the other post is interesting - but we only have Windows 2000 and Excel 97!

I will try that at home on my office xp ...

[johnske]

Hi,

I had a similar problem in that several user needed to mutate cells on the same worksheet, but werent supposed to be able to change each others data. This can be accomplished by setting different passwords on subranges of the sheet. see thread Allow users to edit range - problem

You might also want to check out this kb entry by MD (It'll also work in 2000, not sure about '97)
HTH,
John

[Killian]

also it nearly crashes my pc (512mb ram) - I think because the sheet that I want to log changes on is , pre any other user intervention, created from scratch every time (ie its data is wiped, and refreshed by a seperate macro) -usually about 1000 rows and 20 columns -and its going to try and log this every time.

well that would have been worth knowing... I suppose the thing to do would be to have the sheet_change code conditional and set a boolean at the start and end of this other macro

'workbook_open or whereever the workbook is set up
Dim LogChanges As Boolean
LogChanges = False
        'initialize worksheet
    LogChanges = True

Private Sub Worksheet_Change(ByVal Target As Range)
    If LogChanges = True Then
        WriteToLog Now, ThisWorkbook.Name, ActiveSheet.Name, Target.Address, CStr(Target.Value), Environ("USERNAME")
    End If
End Sub

You can also similarly select which cell changes you log

Private Sub Worksheet_Change(ByVal Target As Range)
    ' only log changes to column C
    If LogChanges = True And Target.Column = 3 Then
        WriteToLog Now, ThisWorkbook.Name, ActiveSheet.Name, Target.Address, CStr(Target.Value), Environ("USERNAME")
    End If
End Sub