Insert row form button only working twice

Excel_T

New Member
Joined
Jun 9, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
I'm quite new to VBA and excel in general, so maybe a stupid issue, but I created two form buttons. One inserts an extra row with formulas and format, and the other one deletes a row. My problem is with the one that inserts a row. If I insert two rows there is no problem, when I try to insert a third row it gives me error 400. Can somebody explain what I'm doing wrong?

This is the only VBA I use:

Sub AddNewRow()

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
r.Offset(-2).EntireRow.Copy
r.Offset(-1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
r.Offset(-2).EntireRow.PasteSpecial xlPasteFormats
r.Offset(-2).EntireRow.Borders(xlEdgeTop).LineStyle = xlNone
Application.CutCopyMode = False

End Sub

Sub DeleteLastRow()

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
r.Offset(-2).EntireRow.Delete
Application.CutCopyMode = False

End Sub
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
@Excel_T No error for me if I use the insert code.
The delete code is also fine until the button top left becomes 2 in which case it errors because of the -2 offset.

Do you have any oddball features in your sheet data or formatting?
 
Upvote 0
@Excel_T No error for me if I use the insert code.
The delete code is also fine until the button top left becomes 2 in which case it errors because of the -2 offset.

Do you have any oddball features in your sheet data or formatting?
Hi, no oddball features. I have a couple of merged cells containing some formula's referencing other cells and in one cell an index match function. Nothing really what could screw up the code.
 
Upvote 0
@Excel_T No error for me if I use the insert code.
The delete code is also fine until the button top left becomes 2 in which case it errors because of the -2 offset.

Do you have any oddball features in your sheet data or formatting?
This is basicly what I have. 1st cell is freetext. 2nd cell is reference to other table. 3-6 is a dropdown. 7 is a reference and 8 a calculation formula
 

Attachments

  • Capture.PNG
    Capture.PNG
    7.6 KB · Views: 3
Upvote 0
I'm quite new to VBA and excel in general, so maybe a stupid issue, but I created two form buttons. One inserts an extra row with formulas and format, and the other one deletes a row. My problem is with the one that inserts a row. If I insert two rows there is no problem, when I try to insert a third row it gives me error 400. Can somebody explain what I'm doing wrong?

This is the only VBA I use:

Sub AddNewRow()

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
r.Offset(-2).EntireRow.Copy
r.Offset(-1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
r.Offset(-2).EntireRow.PasteSpecial xlPasteFormats
r.Offset(-2).EntireRow.Borders(xlEdgeTop).LineStyle = xlNone
Application.CutCopyMode = False

End Sub

Sub DeleteLastRow()

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
r.Offset(-2).EntireRow.Delete
Application.CutCopyMode = False

End Sub
I tried copying a the part to a different sheet in the same document. And there it works perfect.... What kind of formula or setting could screw up this function. Still no other VBA throughout the document...
 
Upvote 0
Hi, I've had a little play with merged cells and formulas and cannot get anything I do to upset the code. Nothing immediately springs to my mind.
I can only suggest that you duplicate the sheet. Then do stuff, one by one,such as un-merge the merged cells, remove any suspect formula, and see if anything has a positive impact.
Just out of interest, which line is it at when it generates the error?

Also, add a debug line as below. It will print the Top Left cell addressin the vba editor Immediate pane, which may or may not help.
VBA Code:
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Debug.Print r.Address  '<<<<

r.Offset(-2).EntireRow.Copy
 
Upvote 0
If you add the line with stop like
VBA Code:
Sub AddNewRow()

Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Stop
r.Offset(-2).EntireRow.Copy
r.Offset(-1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
r.Offset(-2).EntireRow.PasteSpecial xlPasteFormats
r.Offset(-2).EntireRow.Borders(xlEdgeTop).LineStyle = xlNone
Application.CutCopyMode = False

End Sub
& click the button, you can step through the code using F8, when it fails what is the error number, message & what line does it fail on?
 
Upvote 0
Hi, I've had a little play with merged cells and formulas and cannot get anything I do to upset the code. Nothing immediately springs to my mind.
I can only suggest that you duplicate the sheet. Then do stuff, one by one,such as un-merge the merged cells, remove any suspect formula, and see if anything has a positive impact.
Just out of interest, which line is it at when it generates the error?

Also, add a debug line as below. It will print the Top Left cell addressin the vba editor Immediate pane, which may or may not help.
VBA Code:
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
Debug.Print r.Address  '<<<<

r.Offset(-2).EntireRow.Copy
I found the issue. Somehow some text got into the almost absolute bottom right corner of the sheet (cell XFC1048574)... I deleted the text and now it works. Thank you for your help.
 
Upvote 0
Solution
I found the issue. Somehow some text got into the almost absolute bottom right corner of the sheet (cell XFC1048574)... I deleted the text and now it works. Thank you for your help.
Thank you for trying and thinking with me. Facepalming myself right now... But what to do, let's say it's all in the learning curve... Thank you again
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

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
Back
Top