CODEWITHSUNDEEP
vbaexcel
laooglee
laooglee

Reputation: 147

RunTime errors and Cells() property fails inside Workbook_SheetChange

I'm trying to write a simple tracking procedure, but can't teach it properly fill the report line by line.

The idea is:

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

'define last row in report
    <proper code for it>

    'fill in "last row +1" with tracking info
     <proper code for it as well>

End Sub

So i've tried two options:

1) With Cells() property (instead of Offset there might be just LastRow + 1 for row index)

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

    LastRow = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row

    ThisWorkbook.Sheets(1).Cells(LastRow, 1).Offset(1, 0).Value = 1

End Sub

And it results in multiple rows filling with desired values. Strangely it always took 93 rows down of the chosen cell after each change in the worksheet (so 93 186 279 372 465 558).

2) With Range() property:

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

    LastRow = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row

    ThisWorkbook.Sheets(1).Range("A1").Offset(LastRow, 1).Value = 1

End Sub

Then it properly fill required cell, but then constantly results in

run-time error '-2147417848 (80010108): method range of object _worksheet failed

And in one of three times it also make Excel crash.

Upvotes: 0

Views: 56

Answers (2)

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

You are probably re-triggering Events. Disable events before:

ThisWorkbook.Sheets(1).Cells(LastRow, 1).Offset(1, 0).Value = 1

and re-enable them afterwards.

Upvotes: 0

ChipsLetten
ChipsLetten

Reputation: 2953

Your code triggers the SheetChange event because the code writes to the sheet so the code creates a recurring loop. Try turning events off and then back on again.

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

Application.EnableEvents = False

LastRow = ThisWorkbook.Sheets(1).Cells(ThisWorkbook.Sheets(1).Rows.Count, 1).End(xlUp).Row

ThisWorkbook.Sheets(1).Cells(LastRow, 1).Offset(1, 0).Value = 1
Application.EnableEvents = True

End Sub

Upvotes: 1

Related Questions