Can I list which sheets/cells have changed?
 

I wanted to keep a small log of what shhets and possibly what cells changed
each time the workbook is opend. The reason for this is that as a teacher I
want to see how the students interact with the sheets when solving problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas

Can I list which sheets/cells have changed?
 

If you share the workbook under tools, there is an option to track history.

sharing the workbook does impose some limitations on what can be done with
it.

See help for details.
Otherwise, look at the Change event and selectionchange event

http://www.cpearson.com/excel/events.htm
Chip Pearson's page on events.

--
Regards,
Tom Ogilvy


"Rory" wrote in message
...
I wanted to keep a small log of what shhets and possibly what cells
changed
each time the workbook is opend. The reason for this is that as a teacher
I
want to see how the students interact with the sheets when solving
problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas

Can I list which sheets/cells have changed?
 

Rory,

You could save the details on a hidden sheet. Catch the changes using
worksheet change events, and write before and after to the sheet. You will
need to think about housekeeping though.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rory" wrote in message
...
I wanted to keep a small log of what shhets and possibly what cells
changed
each time the workbook is opend. The reason for this is that as a teacher
I
want to see how the students interact with the sheets when solving
problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas

Can I list which sheets/cells have changed?
 

You could add an event procedure Workbook_SheetChange - this will trigger any
time there is a change in any cell in the workbook, and the parameters passed
to it will allow you to trap both the sheet that was changed and the specific
range. I would then make a "log entry" that lists the user (from your API
calls), the sheet, and the range - possibly even the cell contents after the
change. You could write these to a log file. Here is a "bare bones" example
(CurrentUser and ComputerName would be from your API calls):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim LogRange As Range, Entries As Integer
Open "F:\klog.txt" For Append As #1

Set LogRange = Sheets("Log").Range("A1").CurrentRegion
Entries = LogRange.Rows.Count

Write #1, ":" & CurrentUser() & ";" & ComputerName() & ";" & Sh.Name & ";" &
Target.Address & ";" & Target.Range("A1").Formula

Close #1

End Sub

This could be customized to your needs - HTH!
K Dales


"Rory" wrote:

I wanted to keep a small log of what shhets and possibly what cells changed
each time the workbook is opend. The reason for this is that as a teacher I
want to see how the students interact with the sheets when solving problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas

Can I list which sheets/cells have changed?
 

Just noticed my code snippet had in it some excess baggage from a prior
attempt to use a worksheet range as the log; will mark them below (can be
eliminated)

"K Dales" wrote:

You could add an event procedure Workbook_SheetChange - this will trigger any
time there is a change in any cell in the workbook, and the parameters passed
to it will allow you to trap both the sheet that was changed and the specific
range. I would then make a "log entry" that lists the user (from your API
calls), the sheet, and the range - possibly even the cell contents after the
change. You could write these to a log file. Here is a "bare bones" example
(CurrentUser and ComputerName would be from your API calls):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' NOT NEEDED: Dim LogRange As Range, Entries As Integer
Open "F:\klog.txt" For Append As #1

' NOT NEEDED: Set LogRange = Sheets("Log").Range("A1").CurrentRegion
' NOT NEEDED: Entries = LogRange.Rows.Count

Write #1, ":" & CurrentUser() & ";" & ComputerName() & ";" & Sh.Name & ";" &
Target.Address & ";" & Target.Range("A1").Formula

Close #1

End Sub

This could be customized to your needs - HTH!
K Dales


"Rory" wrote:

I wanted to keep a small log of what shhets and possibly what cells changed
each time the workbook is opend. The reason for this is that as a teacher I
want to see how the students interact with the sheets when solving problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas

Can I list which sheets/cells have changed?
 

K:

Thanks for the snippet. It worked like a charm with the API call Data. Just
what I was Looking for.

"K Dales" wrote:

Just noticed my code snippet had in it some excess baggage from a prior
attempt to use a worksheet range as the log; will mark them below (can be
eliminated)

"K Dales" wrote:

You could add an event procedure Workbook_SheetChange - this will trigger any
time there is a change in any cell in the workbook, and the parameters passed
to it will allow you to trap both the sheet that was changed and the specific
range. I would then make a "log entry" that lists the user (from your API
calls), the sheet, and the range - possibly even the cell contents after the
change. You could write these to a log file. Here is a "bare bones" example
(CurrentUser and ComputerName would be from your API calls):

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

' NOT NEEDED: Dim LogRange As Range, Entries As Integer
Open "F:\klog.txt" For Append As #1

' NOT NEEDED: Set LogRange = Sheets("Log").Range("A1").CurrentRegion
' NOT NEEDED: Entries = LogRange.Rows.Count

Write #1, ":" & CurrentUser() & ";" & ComputerName() & ";" & Sh.Name & ";" &
Target.Address & ";" & Target.Range("A1").Formula

Close #1

End Sub

This could be customized to your needs - HTH!
K Dales


"Rory" wrote:

I wanted to keep a small log of what shhets and possibly what cells changed
each time the workbook is opend. The reason for this is that as a teacher I
want to see how the students interact with the sheets when solving problems.

I ahve already ebeen able to incorporate the get network username and
computername API calls, but I can't seem to figure out how to record the
changes.

Any Ideas