Vb-how To Moniter Changes Made In Excel By Using Vb 6.0 Software

[smugcool]

HI all,

I wanted to monitor changes made in any excel file of each and every cell by various users in networking system.

kindly provide some guidelines how to create the software.

Regards
Anup kumar

[Killer42]

I wanted to monitor changes made in any excel file of each and every cell by various users in networking system.

kindly provide some guidelines how to create the software.

It might be simplest to put something in the Worksheet_Chang e event of the worksheet(s) in question. For example, you could append an entry to a log (text) file each time.

[smugcool]

It might be simplest to put something in the Worksheet_Chang e event of the worksheet(s) in question. For example, you could append an entry to a log (text) file each time.

I didn't get dear.

Plz explain this thing in a bit more broadway. As i am very much new to Vb.

[Killer42]

I didn't get dear.

Plz explain this thing in a bit more broadway. As i am very much new to Vb.

Ok, well in this case we'll actually be talking about VBA. That's "Visual Basic for Applications", which is a version of VB built into MS Office applications to do scripting (automating tasks) by writing "macro" code.

Let's have a go at throwing together a simple little example macro for Excel which will append the details of each change made to a worksheet into a text file.

To create this, follow the following steps. (Hopefully they won't vary too much on your system, but we'll see).

• open a new worksheet in Excel.
• Pull down the Tools menu, expand the [b]Macro submenu and select Visual Basic Editor.
• In the Project Explorer window (the one probably at top-left which lists what's in the project) double-click on ThisWorkbook.
• You should get a blank code window on the right.
• At the top of the code window, pull down the left-hand listbox and select WorkBook.
• Pull down the right-hand listbox and select SheetChange.
• You are now in the code window for the workbook's SheetChange event procedure. Any code you enter here will be executed each time a change is made on any of the worksheets in this workbook.
• Paste in the following code. This is a very simple example, but will provide you with a starting point. You can then take this code and play with it.
  

  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim W As Worksheet
    Set W = Sh
    Dim C As Range
    Dim Fnum As Long
    Fnum = FreeFile
    Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum
    For Each C In Target.Cells
      With C
        'Debug.Print , W.Name, .Column, .Row, .Value, .Value2
        Print #Fnum, W.Name; vbTab; .Column; vbTab; .Row; vbTab; .Formula; vbTab; .Value
      End With
    Next
    Set W = Nothing
    Set C = Nothing
    Close #Fnum
  End Sub
   

[smugcool]

Ok, well in this case we'll actually be talking about VBA. That's "Visual Basic for Applications", which is a version of VB built into MS Office applications to do scripting (automating tasks) by writing "macro" code.

Let's have a go at throwing together a simple little example macro for Excel which will append the details of each change made to a worksheet into a text file.

To create this, follow the following steps. (Hopefully they won't vary too much on your system, but we'll see).

• open a new worksheet in Excel.
• Pull down the Tools menu, expand the [b]Macro submenu and select Visual Basic Editor.
• In the Project Explorer window (the one probably at top-left which lists what's in the project) double-click on ThisWorkbook.
• You should get a blank code window on the right.
• At the top of the code window, pull down the left-hand listbox and select WorkBook.
• Pull down the right-hand listbox and select SheetChange.
• You are now in the code window for the workbook's SheetChange event procedure. Any code you enter here will be executed each time a change is made on any of the worksheets in this workbook.
• Paste in the following code. This is a very simple example, but will provide you with a starting point. You can then take this code and play with it.
  

  Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim W As Worksheet
    Set W = Sh
    Dim C As Range
    Dim Fnum As Long
    Fnum = FreeFile
    Open "C:\Temp\ChangeLog.txt" For Append Access Read Write Lock Write As #Fnum
    For Each C In Target.Cells
      With C
        'Debug.Print , W.Name, .Column, .Row, .Value, .Value2
        Print #Fnum, W.Name; vbTab; .Column; vbTab; .Row; vbTab; .Formula; vbTab; .Value
      End With
    Next
    Set W = Nothing
    Set C = Nothing
    Close #Fnum
  End Sub
   

Ya thanx dear its working. But there is one problem, since this file is in networking system, i wanted the name of the User who will modify these changes. Is It possible?

[Killer42]

Ya thanx dear its working. But there is one problem, since this file is in networking system, i wanted the name of the User who will modify these changes. Is It possible?

Probably. Have a look through the properties that are available. Um... Application.Use rName might be what you want.

[smugcool]

Probably. Have a look through the properties that are available. Um... Application.Use rName might be what you want.

Thanx a lot Dude.Gr8 mind.It has worked.One more favour if you can.I also want the Date and Time of changes made.Plzzzzzzzz zz help.

[Killer42]

Thanx a lot Dude.Gr8 mind.It has worked.One more favour if you can.I also want the Date and Time of changes made.Plzzzzzzzz zz help.

Check out the Now() function. And you may need to use Format() on it.

Um... Now is from VB6. In Excel, you might have to use something else. Check the doco for system date and time.

[smugcool]

Check out the Now() function. And you may need to use Format() on it.

Um... Now is from VB6. In Excel, you might have to use something else. Check the doco for system date and time.

many many thanx to you.Its working.See you later. Bye bye

[smugcool]

many many thanx to you.Its working.See you later. Bye bye

Hi,
Can u tell me now how can i get the "logfile" datewise.I mean i had specified a path of a text file.But is it not possible that certain VBA command can generate the text file automatcally datewise format.

Regards
Anup