Auto-Insert

[kirkm]

Could anyone help with some VB code to do the following,

After double clicking a cell in say Row 17, a new row 18 is inserted.

Thanks - Kirk

 

[JLGWhiz]

I think you would have to use a control object of some type, where you would
select the row for the insert to execute on and then click the control which
would initiate the insertion based on the selection. To use double click on
a point on the worksheet would require deactivating the current double click
event which allows edit access to the active cell.

 

[OssieMac]

One of the following will do it. Note that it inserts a row under the row
that is double clicked.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancel Edit mode started by double click

ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown

End Sub

The following is similar but will only run if you double click column A.

Private Sub Worksheet_BeforeDoubleClick _
(ByVal Target As Range, Cancel As Boolean)

Cancel = True 'Cancel Edit mode started by double click

If Target.Column = 1 Then
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=xlDown
End If

End Sub

 

[OssieMac]

Forgot to say right click on the worksheet name tab and select View Code and
insert one of the macros into the editor and then close the editor (X in red
background top right of screen)

 

[JLGWhiz]

Well, shut my mouth! This old dog just learned a new trick. I never thought
about just stepping past the cell edit. Way to go Ossie.

 

[kirkm]

Thank you both very much, it worked perfectly
Cheers - Kirk