|
If a Row is inserted or is a row is deleted then......
Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
no. but maby a workaround :
If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
a little change to handle deleting more than 1 row
Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub "excelent" skrev: no. but maby a workaround : If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
Hi, I apoligize; I should have mentioned that I am only concerned with rows within specific named ranges. Also, I have some code in the change event; however I only want the code to fire "if" I insert or delete a row but the code is firing with every change. ??? Here is what I have: Dim Rng1 As Range Set Rng1 = Range("Sh1billsW") If Rng1.Rows.Count + 1 Then 'I will be putting different code in here when I am sure this is fine MsgBox "Row count has changed" MsgBox "Number of Rows = " & Rng1.Rows.Count Rng1.Rows.Select End If Thank you. Mark "excelent" wrote: a little change to handle deleting more than 1 row Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub "excelent" skrev: no. but maby a workaround : If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
ok here is modifyed code,
Define name Rng1 in a second sheet (in my code Sheet2) put same number in A1 as rows in Rng1 (u can hide this sheet) But still the code have to be in sheet1-code-module Private Sub Worksheet_Change(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub "ML" skrev: Hi, I apoligize; I should have mentioned that I am only concerned with rows within specific named ranges. Also, I have some code in the change event; however I only want the code to fire "if" I insert or delete a row but the code is firing with every change. ??? Here is what I have: Dim Rng1 As Range Set Rng1 = Range("Sh1billsW") If Rng1.Rows.Count + 1 Then 'I will be putting different code in here when I am sure this is fine MsgBox "Row count has changed" MsgBox "Number of Rows = " & Rng1.Rows.Count Rng1.Rows.Select End If Thank you. Mark "excelent" wrote: a little change to handle deleting more than 1 row Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub "excelent" skrev: no. but maby a workaround : If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
Hi Ex Thank you for the reply; Let me see if I have this correct. You are saying in another sheet, other then the one I will be working on; go to Cell A1 and actually type the number (Integer) into that cell, that represents the number (count) of rows Then the code will comapre the named range to that Integer? Also, I think you posted the code twice, correct? are they both the same? I have (named range) Rng1 on Worksheet 1 and I want to do some comparisons to that range with other named ranges. If something happens to rng1, then do something with the other ranges So, may be as Rng1 is being modified, I can have that Integer in A1 update; what do you think? I am not sure I like this method because any number of ranges (3 named so far) can be changed, so there is really no constant value that I want to committ to. Mark "excelent" wrote: ok here is modifyed code, Define name Rng1 in a second sheet (in my code Sheet2) put same number in A1 as rows in Rng1 (u can hide this sheet) But still the code have to be in sheet1-code-module Private Sub Worksheet_Change(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub "ML" skrev: Hi, I apoligize; I should have mentioned that I am only concerned with rows within specific named ranges. Also, I have some code in the change event; however I only want the code to fire "if" I insert or delete a row but the code is firing with every change. ??? Here is what I have: Dim Rng1 As Range Set Rng1 = Range("Sh1billsW") If Rng1.Rows.Count + 1 Then 'I will be putting different code in here when I am sure this is fine MsgBox "Row count has changed" MsgBox "Number of Rows = " & Rng1.Rows.Count Rng1.Rows.Select End If Thank you. Mark "excelent" wrote: a little change to handle deleting more than 1 row Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub "excelent" skrev: no. but maby a workaround : If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
If a Row is inserted or is a row is deleted then......
Just cheked that only 1 of the code is working ok - this one :
Private Sub Worksheet_Change(ByVal Target As Range) ..... "ML0940" skrev: Hi Ex Thank you for the reply; Let me see if I have this correct. You are saying in another sheet, other then the one I will be working on; go to Cell A1 and actually type the number (Integer) into that cell, that represents the number (count) of rows YES Then the code will comapre the named range to that Integer? YES Also, I think you posted the code twice, correct? are they both the same? NO they are not the same, well not first line anyway - but as i say 1 is ok I have (named range) Rng1 on Worksheet 1 and I want to do some comparisons to that range with other named ranges. If something happens to rng1, then do something with the other ranges So, may be as Rng1 is being modified, I can have that Integer in A1 update; IF u insert or delete Rows in Rng1, then A1 is updated by my code what do you think? I am not sure I like this method because any number of ranges (3 named so far) can be changed, so there is really no constant value that I want to committ to. Well Rng1 allways set equal to sheet? A1 I dont know any other way to check for insert/delete rows. Mark "excelent" wrote: ok here is modifyed code, Define name Rng1 in a second sheet (in my code Sheet2) put same number in A1 as rows in Rng1 (u can hide this sheet) But still the code have to be in sheet1-code-module Private Sub Worksheet_Change(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Sheet1!Rng1].Rows.Count < [Sheet2!A1] Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo ' Row delete is canceled Else [Sheet2!A1] = [Sheet1!Rng1].Rows.Count 'Row delete is not canceled and Sheet2 A1 set to new rows in Rng1 End If End If End Sub "ML" skrev: Hi, I apoligize; I should have mentioned that I am only concerned with rows within specific named ranges. Also, I have some code in the change event; however I only want the code to fire "if" I insert or delete a row but the code is firing with every change. ??? Here is what I have: Dim Rng1 As Range Set Rng1 = Range("Sh1billsW") If Rng1.Rows.Count + 1 Then 'I will be putting different code in here when I am sure this is fine MsgBox "Row count has changed" MsgBox "Number of Rows = " & Rng1.Rows.Count Rng1.Rows.Select End If Thank you. Mark "excelent" wrote: a little change to handle deleting more than 1 row Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else [Ark1!RowLast].Cut Destination:=Range("A65536") End If End If End Sub "excelent" skrev: no. but maby a workaround : If u have a value somwhere in lasteow, Excel automatic create an error so my code only take care of deleting rows. First put this formula in cell A65536 =row() select cell A65536 name this cell as RowLast Put the folowing code in ur sheet-code-module (Change Ark1 to ur sheetname) Private Sub Worksheet_Change(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) If [Ark1!RowLast].Value < 65536 Then If MsgBox("Warning...ok to delete row ? ", vbYesNo) = vbNo Then Application.Undo Else Range("A65535").Cut Destination:=Range("A65536") End If End If End Sub "ML" skrev: Hi I was wondering if there is a function out there that will allow me to catch a row added and a row deleted event. If a row is deleted then code If a row is inserted Then code End if End if Anyone have something like this? Thank you, Mark |
| All times are GMT +1. The time now is 07:03 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com