Possible to trap an 'Insert Row' via VBA ?
 

So I can apply validation and checks?
TIA

Possible to trap an 'Insert Row' via VBA ?
 

Your question is too unclear on what excactly what you want to do. The only
thing which came to my mind is the "Worksheet_Change" event of the worksheet.

However, my opinion is: it could be very difficult to really "trap an insert
row". Difficult to make it efficient and bullet proof. It'll require a
continuous checking of the pre-change and post-change layout of the
worksheet. It could be less difficult if the structure is highly predictable
and restricted.

Regards,
Edwin Tam

http://www.vonixx.com

"TRB" wrote:

So I can apply validation and checks?
TIA

Possible to trap an 'Insert Row' via VBA ?
 

Thanks 4 setting me in the right direction, Edwin...
To clarify, if a user doesn't use my form for data entry, but inserts a new
row to edit manually, I wanted to add validation to specific cells. Came up
with the following that seems to work (list validation for cells B1:B10):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target.Column = "2" And Target.Row < 10 Then
With Range(Target.Address).Validation
.Add Type:=xlValidateList, Formula1:=Range("E2")
.ErrorTitle = "Ooops"
.ErrorMessage = "You can do it Bubba - Try Again"
End With
End If
Application.EnableEvents = True
End Sub

"Edwin Tam" wrote:

Your question is too unclear on what excactly what you want to do. The only
thing which came to my mind is the "Worksheet_Change" event of the worksheet.

Possible to trap an 'Insert Row' via VBA ?
 

Thanks 4 sending me in the right direction, Edwin! ...

To clarify, I need to provide cell validation if a user doen't use my form
for data input, but inserts a new line and starts editing. Came up with this
that seems to work (list validation for cells B1:B10) :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
Application.EnableEvents = False
If Target.Column = "2" And Target.Row < 10 Then
With Range(Target.Address).Validation
.Add Type:=xlValidateList, Formula1:=Range("E2")
.ErrorTitle = "Ooops"
.ErrorMessage = "You can do it Bubba - Try Again"
End With
End If
Application.EnableEvents = True
End Sub

Many thanks, Tom

"Edwin Tam" wrote:

Your question is too unclear on what excactly what you want to do. The only
thing which came to my mind is the "Worksheet_Change" event of the worksheet.

However, my opinion is: it could be very difficult to really "trap an insert
row". Difficult to make it efficient and bullet proof. It'll require a
continuous checking of the pre-change and post-change layout of the
worksheet. It could be less difficult if the structure is highly predictable
and restricted.

Regards,
Edwin Tam

http://www.vonixx.com

"TRB" wrote:

So I can apply validation and checks?
TIA