vipulhumein
06-04-2013, 01:42 AM
hi
in my sheet my target column(i.e column L) had a formula which is hidden so that no one can access or edit the formula and the formula is H-O . I WANT THAT IF IN MY TARGET COLUMN ANY VALUE MORE THAN 0 THAN AUTO INSERT ROW
please help me by rectifying the below vba
thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Const DISP_PWD = "123"
Dim i As Long
If Target.Column = 12 And Target.Row > 5 Then
If Target > 0 Then
If MsgBox("Add new row?", vbYes, "Change Sheet") Then
Updating = True
ActiveSheet.Unprotect Password:=DISP_PWD
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column)
Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
For i = 1 To 10
Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
Next i
CopyCells 11
CopyCells 12
CopyCells 17
CopyCells 19
CopyCells 24
CopyCells 25
CopyCells 26
CopyCells 27
CopyCells 28
CopyCells 30
Cells(ActiveCell.Row, 20).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=DISP_PWD
Updating = False
End If
End If
End If
end sub
in my sheet my target column(i.e column L) had a formula which is hidden so that no one can access or edit the formula and the formula is H-O . I WANT THAT IF IN MY TARGET COLUMN ANY VALUE MORE THAN 0 THAN AUTO INSERT ROW
please help me by rectifying the below vba
thanks in advance
Private Sub Worksheet_Change(ByVal Target As Range)
Const DISP_PWD = "123"
Dim i As Long
If Target.Column = 12 And Target.Row > 5 Then
If Target > 0 Then
If MsgBox("Add new row?", vbYes, "Change Sheet") Then
Updating = True
ActiveSheet.Unprotect Password:=DISP_PWD
Cells(ActiveCell.Row + 1, ActiveCell.Column).Select
Rows(ActiveCell.Row & ":" & ActiveCell.Row).Insert
Cells(ActiveCell.Row, ActiveCell.Column) = Cells(ActiveCell.Row + 1, ActiveCell.Column)
Cells(ActiveCell.Row + 1, ActiveCell.Column) = ""
For i = 1 To 10
Cells(ActiveCell.Row, i).Formula = Cells(ActiveCell.Row - 1, i).Formula
Next i
CopyCells 11
CopyCells 12
CopyCells 17
CopyCells 19
CopyCells 24
CopyCells 25
CopyCells 26
CopyCells 27
CopyCells 28
CopyCells 30
Cells(ActiveCell.Row, 20).Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:=DISP_PWD
Updating = False
End If
End If
End If
end sub