audit trail
 

I need to check to see if a user exceeded a workload and then realized it and
went back and changed their entry. Is there a way to do this?

The sheet sums several columns and if the value is greater than 100 the row
highlights red. What I need to now is if they ever exceeded 100 and went
back and changed some of the columns so they were under 100. I hope this
makes sense.

Thanks,
Mike

audit trail
 

This is a simple example assuming the sum formulas are in cells A10 thru D10.
This macro examines the sums and the date is inserted in the cell below if
the sum exceeds 100. This date does not go away after the sum is corrected:

Private Sub Worksheet_Calculate()
Set r = Range("A10:D10")
For Each rr In r
If rr.Value 100 Then
Application.EnableEvents = False
rr.Offset(1, 0).Value = Date
Application.EnableEvents = True
End If
Next
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200770


"MIke" wrote:

I need to check to see if a user exceeded a workload and then realized it and
went back and changed their entry. Is there a way to do this?

The sheet sums several columns and if the value is greater than 100 the row
highlights red. What I need to now is if they ever exceeded 100 and went
back and changed some of the columns so they were under 100. I hope this
makes sense.

Thanks,
Mike

audit trail
 

Gary's Student,
Thanks for the help. The macro you gave me works great but I tried to
expand it to check an additional cell value in another column and I cant get
it to work. I have some code that I used to lock cells after a value was
exceeded in a cell but when I used this code I had to spend alot of time
unlocking sheets because they mistakenly entered the wrong number I would
rather use the date audit that you supplied. Is there an easy way to convert
it into putting the date into a cell instead of locking it? I appreciate all
of your help. Mike

'Private Sub Worksheet_Change(ByVal Target As Range)
'Dim myCell As Range
' On Error GoTo ws_exit:
' Application.EnableEvents = False
' ActiveSheet.Unprotect Password:="justme"
' For Each myCell In Range("O7:O37")
' If myCell.Value [100] Or myCell.Offset(0, 2).Value [12.5] Then
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True
' Else
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False
' End If
' Next myCell
'ActiveSheet.Protect Password:="justme"
'
'ws_exit:
'
' Application.EnableEvents = True
'End Sub



"Gary''s Student" wrote:

This is a simple example assuming the sum formulas are in cells A10 thru D10.
This macro examines the sums and the date is inserted in the cell below if
the sum exceeds 100. This date does not go away after the sum is corrected:

Private Sub Worksheet_Calculate()
Set r = Range("A10:D10")
For Each rr In r
If rr.Value 100 Then
Application.EnableEvents = False
rr.Offset(1, 0).Value = Date
Application.EnableEvents = True
End If
Next
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200770


"MIke" wrote:

I need to check to see if a user exceeded a workload and then realized it and
went back and changed their entry. Is there a way to do this?

The sheet sums several columns and if the value is greater than 100 the row
highlights red. What I need to now is if they ever exceeded 100 and went
back and changed some of the columns so they were under 100. I hope this
makes sense.

Thanks,
Mike

audit trail
 

I will look at your code tomorrow.
--
Gary''s Student - gsnu200770


"MIke" wrote:

Gary's Student,
Thanks for the help. The macro you gave me works great but I tried to
expand it to check an additional cell value in another column and I cant get
it to work. I have some code that I used to lock cells after a value was
exceeded in a cell but when I used this code I had to spend alot of time
unlocking sheets because they mistakenly entered the wrong number I would
rather use the date audit that you supplied. Is there an easy way to convert
it into putting the date into a cell instead of locking it? I appreciate all
of your help. Mike

'Private Sub Worksheet_Change(ByVal Target As Range)
'Dim myCell As Range
' On Error GoTo ws_exit:
' Application.EnableEvents = False
' ActiveSheet.Unprotect Password:="justme"
' For Each myCell In Range("O7:O37")
' If myCell.Value [100] Or myCell.Offset(0, 2).Value [12.5] Then
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True
' Else
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False
' End If
' Next myCell
'ActiveSheet.Protect Password:="justme"
'
'ws_exit:
'
' Application.EnableEvents = True
'End Sub



"Gary''s Student" wrote:

This is a simple example assuming the sum formulas are in cells A10 thru D10.
This macro examines the sums and the date is inserted in the cell below if
the sum exceeds 100. This date does not go away after the sum is corrected:

Private Sub Worksheet_Calculate()
Set r = Range("A10:D10")
For Each rr In r
If rr.Value 100 Then
Application.EnableEvents = False
rr.Offset(1, 0).Value = Date
Application.EnableEvents = True
End If
Next
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200770


"MIke" wrote:

I need to check to see if a user exceeded a workload and then realized it and
went back and changed their entry. Is there a way to do this?

The sheet sums several columns and if the value is greater than 100 the row
highlights red. What I need to now is if they ever exceeded 100 and went
back and changed some of the columns so they were under 100. I hope this
makes sense.

Thanks,
Mike

audit trail
 

If the users are bright enough to re-enter data to get below a certain
threshhold, they would be bright enough to delete the date stamp from a cell
once they got back below 100.


Gord Dibben MS Excel MVP

On Tue, 26 Feb 2008 11:20:01 -0800, MIke wrote:

Gary's Student,
Thanks for the help. The macro you gave me works great but I tried to
expand it to check an additional cell value in another column and I cant get
it to work. I have some code that I used to lock cells after a value was
exceeded in a cell but when I used this code I had to spend alot of time
unlocking sheets because they mistakenly entered the wrong number I would
rather use the date audit that you supplied. Is there an easy way to convert
it into putting the date into a cell instead of locking it? I appreciate all
of your help. Mike

'Private Sub Worksheet_Change(ByVal Target As Range)
'Dim myCell As Range
' On Error GoTo ws_exit:
' Application.EnableEvents = False
' ActiveSheet.Unprotect Password:="justme"
' For Each myCell In Range("O7:O37")
' If myCell.Value [100] Or myCell.Offset(0, 2).Value [12.5] Then
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = True
' Else
' Range(myCell.Offset(0, -1), myCell.Offset(0, -13)).Locked = False
' End If
' Next myCell
'ActiveSheet.Protect Password:="justme"
'
'ws_exit:
'
' Application.EnableEvents = True
'End Sub



"Gary''s Student" wrote:

This is a simple example assuming the sum formulas are in cells A10 thru D10.
This macro examines the sums and the date is inserted in the cell below if
the sum exceeds 100. This date does not go away after the sum is corrected:

Private Sub Worksheet_Calculate()
Set r = Range("A10:D10")
For Each rr In r
If rr.Value 100 Then
Application.EnableEvents = False
rr.Offset(1, 0).Value = Date
Application.EnableEvents = True
End If
Next
End Sub


Because it is worksheet code, it is very easy to install and use:

1. right-click the tab name near the bottom of the window
2. select View Code - this brings up a VBE window
3. paste the stuff in and close the VBE window

If you save the workbook, the macro will be saved with it.

To remove the macro:

1. bring up the VBE windows as above
2. clear the code out
3. close the VBE window

To learn more about macros in general, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

To learn more about Event Macros (worksheet code), see:

http://www.mvps.org/dmcritchie/excel/event.htm


--
Gary''s Student - gsnu200770


"MIke" wrote:

I need to check to see if a user exceeded a workload and then realized it and
went back and changed their entry. Is there a way to do this?

The sheet sums several columns and if the value is greater than 100 the row
highlights red. What I need to now is if they ever exceeded 100 and went
back and changed some of the columns so they were under 100. I hope this
makes sense.

Thanks,
Mike