N
Nozza
I'm hitting my head against a brick wall trying to get my subroutine
to work, so I'm coming for help.
I use many spreadsheets in a school and need to track pupils' work as
they work on assignments, so I have got some trackers that enable me
to see how they are all getting along. One of the problems I have had
is being able to track progress within a lesson. With 30 pupils and
multiple pieces of work to check, it is easy for me to be able to miss
a couple of pupils who manage to dodge showing me any work. So I wrote
a routine (below) that enables me to use conditional formatting to
highlight those pupils who have yet to have any changes in a row of
data against their name.
It works fine.
So I thought I would adapt it a little. Just a small change....
What I want the routine to do is add a row of data to a second
worksheet in the same workbook. It needs to find the last row of the
worksheet, move down a row, and then add the same details that are
written to cells in the routine. And maybe the application.user as
well.
But I just cannot figure out how to do it.
I've turned error checking back on by commenting out the On Error
statement in the code below (so I can attempt to see what's going
wrong)
The second sheet I have named ActivityLog.
I added a variable called NextRow
Dim NextRowInActivityLog As Long
but when I issued the command
NextRowInActivityLog = Range("ActivityLog!A65536").End(xlUp).Row + 1
I get
Runtime Error 1004
Method Range of object '_Worksheet' failed
And then any code where I attempt to "force" an update to the second
sheet (by explicitly stating the cell to update. I have tried using
With ("ActivityLog"), Sheets("ActivityLog").Select etc but am
stumbling around and not understanding what I am doing any more. It's
kinda trial and error...
I want to find the next blank row in the second workbook, and then add
a line with the details shown on the first sheet, so I have a complete
log of who got which mark when, and which teacher awarded the mark.
If anyone can suggest improvements to the existing code as well as
helping me write the log, that would be really appreciated.
Thanks for the help
Noz
Existing code is here...
Dim IgnoreThisEvent As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Subroutine to track the marks awarded on a tracker
'080614 PNO
'Declarations
Dim VRangeDateOfMostRecentChange As Range
Dim VRangeDateOfPreviousChange As Range
Dim VRangeMarkAwardedFor As Range
Dim VRangeMarks As Range
Dim VRangeMarkTitle As Range
Dim VRangeToday As Range
Dim NextRowInActivityLog As Long
'Turn off error handling. If an error occurs, then the sheet won't
be updated
'With it's turned on, it won't be updated either - but an error
message will be displayed
'So I'm switching it off to prevent error messages being
displayed.
On Error Resume Next
'Initialisation
Set VRangeDateOfMostRecentChange =
Range("RangeDateOfMostRecentChange")
Set VRangeDateOfPreviousChange =
Range("RangeDateOfPreviousChange")
Set VRangeMarkAwardedFor = Range("RangeMarkAwardedFor")
Set VRangeMarks = Range("RangeMarks")
Set VRangeMarkTitle = Range("RangeMarkTitle")
Set VRangeToday = Range("RangeToday")
If IgnoreThisEvent Or Target.Count > 1 Then Exit Sub
IgnoreThisEvent = True
If Not Intersect(Target, VRangeMarks) Is Nothing Then
'Update the most recent change date
'If there is no current update date, then simply update the
date of update
'Otherwise update both current and previous update dates
If VRangeDateOfMostRecentChange.Cells(Target.Row) = "" Then
VRangeDateOfMostRecentChange.Cells(Target.Row) = Now()
Else
VRangeDateOfPreviousChange.Cells(Target.Row) =
VRangeDateOfMostRecentChange.Cells(Target.Row)
VRangeDateOfMostRecentChange.Cells(Target.Row) = Now()
End If
'Update the reason the mark was awarded
VRangeMarkAwardedFor.Cells(Target.Row) =
VRangeMarkTitle.Cells(Target.Column)
'Put a "Yes" in the RangeToday
VRangeToday.Cells(Target.Row) = "Yup"
End If
IgnoreThisEvent = False
End Sub
to work, so I'm coming for help.
I use many spreadsheets in a school and need to track pupils' work as
they work on assignments, so I have got some trackers that enable me
to see how they are all getting along. One of the problems I have had
is being able to track progress within a lesson. With 30 pupils and
multiple pieces of work to check, it is easy for me to be able to miss
a couple of pupils who manage to dodge showing me any work. So I wrote
a routine (below) that enables me to use conditional formatting to
highlight those pupils who have yet to have any changes in a row of
data against their name.
It works fine.
So I thought I would adapt it a little. Just a small change....
What I want the routine to do is add a row of data to a second
worksheet in the same workbook. It needs to find the last row of the
worksheet, move down a row, and then add the same details that are
written to cells in the routine. And maybe the application.user as
well.
But I just cannot figure out how to do it.
I've turned error checking back on by commenting out the On Error
statement in the code below (so I can attempt to see what's going
wrong)
The second sheet I have named ActivityLog.
I added a variable called NextRow
Dim NextRowInActivityLog As Long
but when I issued the command
NextRowInActivityLog = Range("ActivityLog!A65536").End(xlUp).Row + 1
I get
Runtime Error 1004
Method Range of object '_Worksheet' failed
And then any code where I attempt to "force" an update to the second
sheet (by explicitly stating the cell to update. I have tried using
With ("ActivityLog"), Sheets("ActivityLog").Select etc but am
stumbling around and not understanding what I am doing any more. It's
kinda trial and error...
I want to find the next blank row in the second workbook, and then add
a line with the details shown on the first sheet, so I have a complete
log of who got which mark when, and which teacher awarded the mark.
If anyone can suggest improvements to the existing code as well as
helping me write the log, that would be really appreciated.
Thanks for the help
Noz
Existing code is here...
Dim IgnoreThisEvent As Boolean
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Subroutine to track the marks awarded on a tracker
'080614 PNO
'Declarations
Dim VRangeDateOfMostRecentChange As Range
Dim VRangeDateOfPreviousChange As Range
Dim VRangeMarkAwardedFor As Range
Dim VRangeMarks As Range
Dim VRangeMarkTitle As Range
Dim VRangeToday As Range
Dim NextRowInActivityLog As Long
'Turn off error handling. If an error occurs, then the sheet won't
be updated
'With it's turned on, it won't be updated either - but an error
message will be displayed
'So I'm switching it off to prevent error messages being
displayed.
On Error Resume Next
'Initialisation
Set VRangeDateOfMostRecentChange =
Range("RangeDateOfMostRecentChange")
Set VRangeDateOfPreviousChange =
Range("RangeDateOfPreviousChange")
Set VRangeMarkAwardedFor = Range("RangeMarkAwardedFor")
Set VRangeMarks = Range("RangeMarks")
Set VRangeMarkTitle = Range("RangeMarkTitle")
Set VRangeToday = Range("RangeToday")
If IgnoreThisEvent Or Target.Count > 1 Then Exit Sub
IgnoreThisEvent = True
If Not Intersect(Target, VRangeMarks) Is Nothing Then
'Update the most recent change date
'If there is no current update date, then simply update the
date of update
'Otherwise update both current and previous update dates
If VRangeDateOfMostRecentChange.Cells(Target.Row) = "" Then
VRangeDateOfMostRecentChange.Cells(Target.Row) = Now()
Else
VRangeDateOfPreviousChange.Cells(Target.Row) =
VRangeDateOfMostRecentChange.Cells(Target.Row)
VRangeDateOfMostRecentChange.Cells(Target.Row) = Now()
End If
'Update the reason the mark was awarded
VRangeMarkAwardedFor.Cells(Target.Row) =
VRangeMarkTitle.Cells(Target.Column)
'Put a "Yes" in the RangeToday
VRangeToday.Cells(Target.Row) = "Yup"
End If
IgnoreThisEvent = False
End Sub