Worksheet_Change

[okrob]

Why can't I use this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("Data").Rows.Count < Sheet6.Cells(1, 199).Value Then
Dim x
x = Target.Row

Sheet6.Cells(1, 200).Value = Sheet6.Cells(1, 200).Value & " / " & x
Sheet6.Select

Cells(x, 1).Select' <== This line causes
' a "Select Method of Range Class Failed" error

Selection.EntireRow.Delete

End If
Sheet6.Cells(1, 199).Value = Range("Data").Rows.Count
End Sub

What I'm doing is looking at the sheet that the event is fired from,
getting the target range, determining if a row was deleted, then I
want to go to sheet6 and delete the corresponding row. When
debugging, x = the row that I deleted, but I can't seem to select the
same row on the other sheet...

 

[Guest]

I believe it's because your code is not in Sheet6 and when you use an
unqualifed Object like Cells, it defaults to the Cells in the sheet where the
code is. And since that sheet is not active, you get the error when you try
to select.

If you don't really need to have Sheet6 as the active sheet, you can try
replacing this lines

Sheet6.Select
Cells(x, 1).Select' <== This line causes
Selection.EntireRow.Delete

with this one line:

Sheet6.Cells(x, 1).EntireRow.Delete

 

[Don Guillett]

A worksheet change event fires when the target is changed.

 

[okrob]

I believe it's because your code is not in Sheet6 and when you use an
unqualifed Object like Cells, it defaults to the Cells in the sheet where the
code is. And since that sheet is not active, you get the error when you try
to select.

If you don't really need to have Sheet6 as the active sheet, you can try
replacing this lines

Sheet6.Select
Cells(x, 1).Select' <== This line causes
Selection.EntireRow.Delete

with this one line:

Sheet6.Cells(x, 1).EntireRow.Delete

--
Hope that helps.

Vergel Adriano









- Show quoted text -

I'll check it out and see if it works. Thanks...

 

[okrob]

I believe it's because your code is not in Sheet6 and when you use an
unqualifed Object like Cells, it defaults to the Cells in the sheet where the
code is. And since that sheet is not active, you get the error when you try
to select.

If you don't really need to have Sheet6 as the active sheet, you can try
replacing this lines

Sheet6.Select
Cells(x, 1).Select' <== This line causes
Selection.EntireRow.Delete

with this one line:

Sheet6.Cells(x, 1).EntireRow.Delete

--
Hope that helps.

Vergel Adriano









- Show quoted text -

This worked great... Thanks.
Rob

 

[okrob]

Just so everyone sees the final event...
On workbook_open event, I set Sheet6.Cells(1, 199).Value =
Range("Data").Rows.Count so that it starts out with the correct value
in the cell to begin with in case someone deletes it...
Then I put this in the user worksheet.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim y as String
If Range("Data").Rows.Count < Sheet6.Cells(1, 199).Value Then
y = Target.Address
Sheet6.Range(y).Delete shift:=xlUp
Sheet1.Range(y).Delete shift:=xlUp
End If
Sheet6.Cells(1, 199).Value = Range("Data").Rows.Count
End Sub