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

Billy Baxter

New Member
Joined
Dec 18, 2004
Messages
5
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
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
Try putting this in a standard module & run it, then try double-clicking in the relevant sheet.
VBA Code:
Sub Reset()
   Application.EnableEvents = True
End Sub
 

Billy Baxter

New Member
Joined
Dec 18, 2004
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 18, 2004
Messages
5
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,638
Office Version
  1. 365
Platform
  1. Windows
Glad you've sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,141,818
Messages
5,708,763
Members
421,588
Latest member
Wawie

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top