sujittalukde
06-26-2007, 02:00 AM
Please refer to the post at the following link:
http://www.mrexcel.com/board2/viewtopic.php?t=279062&start=10
Little background:
If a record is deleted from "Master data "Sheet the same record should also get deleted from other sheets which is dependant on master data sheet These dependent sheets are linked to masterdata sheet vide =masterdata!B45 (for eg.).
At that forum some esteemed members have tried to solve the same and succeed to a large extent. the code that has been provided there have identified the dependant rows to be deleted but those codes are not deleting the rows of other sheet automatically on deletion of the record from masterdata sheet.
So my requirement is:
When the user will delete the row from the masterdata sheet the code will delete the record from master data sheet and the code will automatically delete the record from other sheets also.
For ready reference I am quoting the code provided at that forum by Denis:
Sub DeleteRow()
Dim a
Dim intActiveRow As Integer
Dim Sht As Worksheet
Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
If a = vbYes Then
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> "MasterData" Then
Sht.Activate
Sht.Unprotect Password:="abcd"
Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Sht.Protect Password:="abcd"
End If
Next Sht
End If
Application.ScreenUpdating = True
End Sub
This I am posting here to get a solution if some one at this forum can solve it.
In case more clarification is required, please get in touch. I'll feel happy to offer clarification.
http://www.mrexcel.com/board2/viewtopic.php?t=279062&start=10
Little background:
If a record is deleted from "Master data "Sheet the same record should also get deleted from other sheets which is dependant on master data sheet These dependent sheets are linked to masterdata sheet vide =masterdata!B45 (for eg.).
At that forum some esteemed members have tried to solve the same and succeed to a large extent. the code that has been provided there have identified the dependant rows to be deleted but those codes are not deleting the rows of other sheet automatically on deletion of the record from masterdata sheet.
So my requirement is:
When the user will delete the row from the masterdata sheet the code will delete the record from master data sheet and the code will automatically delete the record from other sheets also.
For ready reference I am quoting the code provided at that forum by Denis:
Sub DeleteRow()
Dim a
Dim intActiveRow As Integer
Dim Sht As Worksheet
Application.ScreenUpdating = False
intActiveRow = ActiveCell.Row
ActiveSheet.Unprotect Password:="abcd"
a = MsgBox("Do you really want to delete row ?", vbYesNo + vbCritical, "Delete Confirm !")
If a = vbYes Then
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name <> "MasterData" Then
Sht.Activate
Sht.Unprotect Password:="abcd"
Range("B:B").SpecialCells(xlCellTypeFormulas, xlErrors).EntireRow.Delete
Sht.Protect Password:="abcd"
End If
Next Sht
End If
Application.ScreenUpdating = True
End Sub
This I am posting here to get a solution if some one at this forum can solve it.
In case more clarification is required, please get in touch. I'll feel happy to offer clarification.