Multiple users

[gareth]

Hello Excellers

I have this idea that is actuallygoing to be in motion soon.What I need from the friendly helpers is some kind of code that will track the changes in a worksheet and at the same time track whether or not a specific field has been used already.And if there has been an amendment for a certain customer,it will tell the user immediatly if the customer has been done already?

Is there anyone who can be of assistance to a helpless soul ?

[gareth]

Please note that I will not be using this worksheet alone,there will be about 5 or 5 users involved guys.

[gareth]

Is there nobody that can assist me

Thats odd hey

[Tom Schreiner]

You didn't really give very many details there, Gareth...
Sounds like alot of hard work to me!
You can log any change you want via the worksheet and workbook events...
Just log them to a text file...
Iv'e never done it, but I'm sure it could be done...
You have lot's of fun!
TOm

[gareth]

Thank alot Tom

It is alot of work,I started brievly with it on Saturday.I will let you know how far I get with it.If and when it works,I will share my knowledge with the board to learn from the example.

[Tom Schreiner]

Good Deal.
I'd be interested to know how it turns out.
Thanks

[Brian]

I replied earlier, but somehow it did not go through.

1) Take a look at sharing workbooks (Tools > Share workbook)

2) Below is a simple code I used to flag when the latest change on a line occured. You can expand this to record every cell on a log on another sheet (or add to a string in a cell on the same row of the same sheet). Obviously, if you log from the inception of the sheet, then you can search that log to see the previous value(s) and compare it. I don't know how this would fair with multiple concurrent users (the native excel sharing is probably best for that.) But for multiple, one at a time users, you could record their name with the data.

3) You can obviously e-mail or otherwise notify users of changes. search this board for info on sending e-mails & MrExce tip#16.


Sub Worksheet_Change(ByVal Target As Excel.Range)
'2002-04-24 Created by Brian West
'2002-05-05 Modified to not run when entire row selected
'2002-05-05 Modified to not run when only col 1 selected
'With concept from Mr.Excel: http://www.mrexcel.com/tip006.shtml
'Whenever any cell on THIS SHEET changes,
'Enter the current date & time in Column A of the same row.
'NOTE: When pure text is copied from a text file and then pasted in a cell, this macro does not run!
'NOTE: This does work for copying and pasting a range of cells.

Dim c As Range

'MsgBox Target

'Exit Sub 'Exit sub while making layout changes to spreadsheet

If Target.Cells.Count < 256 Then
For Each c In Target 'Loop through each cell in the target range.
If c.Columns.Count = 1 And c.Column = 1 Then
'skip because only changed the date in A (probably tried to clear it)
Else
ThisRow = c.Row
Range("A" & ThisRow) = Format(Now, "yyyy-mm-dd") 'Enter today's date as text in column A
ActiveSheet.Range("A" & ThisRow).Rows.Calculate 'caclulate this row only
ActiveSheet.Range("C" & ThisRow).Calculate 'caclulate this cell only For some unknown reason this was not calculating based on only the previous line
'MsgBox "Should Calc This Row"
End If
Next c
End If

'Supplied Code From Mr.Excel:
'Run this macro whenever a cell changes
' If Target.Column = 1 Then
' ThisRow = Target.Row
' If Target.Value > 100 Then
' Range("B" & ThisRow).Interior.ColorIndex = 3
' Else
' Range("B" & ThisRow).Interior.ColorIndex = xlColorIndexNone
' End If
' End If

'ALTERNATE FROM MS EXCEL HELP FILE:
'Occurs when cells in any worksheet are changed by the user or by an external link.
'Private Sub object_SheetChange(ByVal Sh As Object, ByVal Source As Range)
'object Application or Workbook. For more information about using events with the Application object, see Using Events with the Application Object.
'Sh A Worksheet object that represents the sheet.
'Source The changed range.

End Sub

[gareth]

Thanks Brian

I will check If it works for me.

Tom,let me know what you think about it.

[gareth]

Its works,

But it is still not I wanted to create.

[gareth]

Are there any new ideas on this one.?