VBA Code - Double Click to insert new row, not working

[Billy Baxter]

Hi

This is a popular code which I have copied and used many times - double-clicking on a row inserts a new row below with formulas etc.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = False
Cancel = True
Target.Offset(1).EntireRow.Insert
Target.EntireRow.Copy Target.Offset(1).EntireRow
On Error Resume Next
Target.Offset(1).EntireRow.SpecialCells(xlConstants).ClearContents
Application.ScreenUpdating = True
End Sub

For some reason and I have tried 3 different books I cannot get it to work so I'm assuming it is not the code (as it has worked very well previously) but perhaps some setting. Macros are enabled.
I am not a VBA expert by any means and I may not have set it up correctly in VBA.
Any help much appreciated.
Thanks
Billy

 

[Fluff]

Hi & welcome to MrExcel.
Try putting this in a standard module & run it, then try double-clicking in the relevant sheet.

Sub Reset()
   Application.EnableEvents = True
End Sub

 

[Billy Baxter]

Hi Fluff,
Thanks for this, I've inserted but no different, I don't know why - could it be to do with it being a shared file?
Any further help appreciated
Regards
Billy

 

[Fluff]

could it be to do with it being a shared file?

Could be, I've never used them so don't know all the limitations.

Is the code in the correct sheet module?

 

[Billy Baxter]

Hi Fluff, sorry for the late reply. Yes the code is in the correct sheet module.
I've given up on it for now and changed the data into a table where the formulas effectively do what I need them; ie easily create a new row and copy all formulas down and across. Thanks for your replies.

 

[Fluff]

Glad you've sorted it & thanks for the feedback.