Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Multiple users

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?


  2. #2
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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



  3. #3
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Is there nobody that can assist me

    Thats odd hey

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Posts
    113
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Brian

    I will check If it works for me.

    Tom,let me know what you think about it.

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Its works,

    But it is still not I wanted to create.



  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Cape Town,South Africa
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Are there any new ideas on this one.?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •