|
Worksheet_Change
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... |
Worksheet_Change
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 "okrob" wrote: 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... |
Worksheet_Change
On May 8, 9:48 am, Vergel Adriano
wrote: 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 "okrob" wrote: 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...- Hide quoted text - - Show quoted text - I'll check it out and see if it works. Thanks... |
Worksheet_Change
On May 8, 9:48 am, Vergel Adriano
wrote: 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 "okrob" wrote: 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...- Hide quoted text - - Show quoted text - This worked great... Thanks. Rob |
Worksheet_Change
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 |
| All times are GMT +1. The time now is 05:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com