Method 'Insert' of object 'Range' failed error
up vote
0
down vote
favorite
I have written a short piece of code that intends to, upon double click of a '+' sign on a spreadsheet, that it should copy the entire row where the '+' sign is and insert a new row directly below that is exactly the same.
If I try to add a row before I've added any text to the sheet there is no problem. But if I add text, not even to the line I'm attempting to copy, and then try to add a row I get a
"Method 'Insert' of object 'Range' failed"
error, even if I have deleted the text before I do the copy. The insert itself seems to actually happen, but just triggers an error. If I use the Delete row first though, not even to delete the row with the text, the code works perfectly.When I debug the Cells(Target.Row + 1, 1).EntireRow.Insert part seems to be the issue.
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:=strPwd
If ActiveCell.Column = 3 And Target.Value = "+" Then
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
For Each Cell In Selection
If Cell.Interior.Color = 13434879 Then
Cell.ClearContents
ElseIf Cell.Interior.Color = Black Then
Cell.ClearContents
End If
Next
Cells(Target.Row + 1, 4).Select
Application.CutCopyMode = False
ElseIf ActiveCell.Column = 2 And Target.Value = "-" Then
Target.EntireRow.Select
Target.EntireRow.Delete
Cells(3, 4).Select
End If
ActiveSheet.Protect Password:=strPwd, UserInterfaceOnly:=True
End Sub
Any assistance would be greatly appreciated. Happy to provide more information if need be.
excel vba excel-vba
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
asked Nov 22 at 3:04
Nic
61
|
show 4 more comments
up vote
0
down vote
favorite
I have written a short piece of code that intends to, upon double click of a '+' sign on a spreadsheet, that it should copy the entire row where the '+' sign is and insert a new row directly below that is exactly the same.
If I try to add a row before I've added any text to the sheet there is no problem. But if I add text, not even to the line I'm attempting to copy, and then try to add a row I get a
"Method 'Insert' of object 'Range' failed"
error, even if I have deleted the text before I do the copy. The insert itself seems to actually happen, but just triggers an error. If I use the Delete row first though, not even to delete the row with the text, the code works perfectly.When I debug the Cells(Target.Row + 1, 1).EntireRow.Insert part seems to be the issue.
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:=strPwd
If ActiveCell.Column = 3 And Target.Value = "+" Then
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
For Each Cell In Selection
If Cell.Interior.Color = 13434879 Then
Cell.ClearContents
ElseIf Cell.Interior.Color = Black Then
Cell.ClearContents
End If
Next
Cells(Target.Row + 1, 4).Select
Application.CutCopyMode = False
ElseIf ActiveCell.Column = 2 And Target.Value = "-" Then
Target.EntireRow.Select
Target.EntireRow.Delete
Cells(3, 4).Select
End If
ActiveSheet.Protect Password:=strPwd, UserInterfaceOnly:=True
End Sub
Any assistance would be greatly appreciated. Happy to provide more information if need be.
excel vba excel-vba
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
asked Nov 22 at 3:04
Nic
61
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside theWorksheet_BeforeDoubleClick()event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.
– K.Dᴀᴠɪs
Nov 22 at 4:02
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
Try justRows(target.row + 1).entireRow.Insert?
– BruceWayne
Nov 22 at 5:47
There's no need to select row before deleting (in general, avoid selections). What is the row number ofTarget.Row?
– JohnyL
Nov 22 at 8:54
|
show 4 more comments
up vote
0
down vote
favorite
up vote
0
down vote
favorite
I have written a short piece of code that intends to, upon double click of a '+' sign on a spreadsheet, that it should copy the entire row where the '+' sign is and insert a new row directly below that is exactly the same.
If I try to add a row before I've added any text to the sheet there is no problem. But if I add text, not even to the line I'm attempting to copy, and then try to add a row I get a
"Method 'Insert' of object 'Range' failed"
error, even if I have deleted the text before I do the copy. The insert itself seems to actually happen, but just triggers an error. If I use the Delete row first though, not even to delete the row with the text, the code works perfectly.When I debug the Cells(Target.Row + 1, 1).EntireRow.Insert part seems to be the issue.
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:=strPwd
If ActiveCell.Column = 3 And Target.Value = "+" Then
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
For Each Cell In Selection
If Cell.Interior.Color = 13434879 Then
Cell.ClearContents
ElseIf Cell.Interior.Color = Black Then
Cell.ClearContents
End If
Next
Cells(Target.Row + 1, 4).Select
Application.CutCopyMode = False
ElseIf ActiveCell.Column = 2 And Target.Value = "-" Then
Target.EntireRow.Select
Target.EntireRow.Delete
Cells(3, 4).Select
End If
ActiveSheet.Protect Password:=strPwd, UserInterfaceOnly:=True
End Sub
Any assistance would be greatly appreciated. Happy to provide more information if need be.
excel vba excel-vba
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
asked Nov 22 at 3:04
Nic
61
I have written a short piece of code that intends to, upon double click of a '+' sign on a spreadsheet, that it should copy the entire row where the '+' sign is and insert a new row directly below that is exactly the same.
If I try to add a row before I've added any text to the sheet there is no problem. But if I add text, not even to the line I'm attempting to copy, and then try to add a row I get a
"Method 'Insert' of object 'Range' failed"
error, even if I have deleted the text before I do the copy. The insert itself seems to actually happen, but just triggers an error. If I use the Delete row first though, not even to delete the row with the text, the code works perfectly.When I debug the Cells(Target.Row + 1, 1).EntireRow.Insert part seems to be the issue.
Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
ActiveSheet.Unprotect Password:=strPwd
If ActiveCell.Column = 3 And Target.Value = "+" Then
Target.EntireRow.Copy
Cells(Target.Row + 1, 1).EntireRow.Insert
Cells(Target.Row + 1, 1).EntireRow.Select
For Each Cell In Selection
If Cell.Interior.Color = 13434879 Then
Cell.ClearContents
ElseIf Cell.Interior.Color = Black Then
Cell.ClearContents
End If
Next
Cells(Target.Row + 1, 4).Select
Application.CutCopyMode = False
ElseIf ActiveCell.Column = 2 And Target.Value = "-" Then
Target.EntireRow.Select
Target.EntireRow.Delete
Cells(3, 4).Select
End If
ActiveSheet.Protect Password:=strPwd, UserInterfaceOnly:=True
End Sub
Any assistance would be greatly appreciated. Happy to provide more information if need be.
excel vba excel-vba
excel vba excel-vba
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
asked Nov 22 at 3:04
Nic
61
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
asked Nov 22 at 3:04
Nic
61
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
edited Nov 22 at 3:57
K.Dᴀᴠɪs
6,753112140
6,753112140
asked Nov 22 at 3:04
Nic
61
asked Nov 22 at 3:04
Nic
61
asked Nov 22 at 3:04
Nic
61
61
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside theWorksheet_BeforeDoubleClick()event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.
– K.Dᴀᴠɪs
Nov 22 at 4:02
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
Try justRows(target.row + 1).entireRow.Insert?
– BruceWayne
Nov 22 at 5:47
There's no need to select row before deleting (in general, avoid selections). What is the row number ofTarget.Row?
– JohnyL
Nov 22 at 8:54
|
show 4 more comments
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside theWorksheet_BeforeDoubleClick()event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.
– K.Dᴀᴠɪs
Nov 22 at 4:02
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
Try justRows(target.row + 1).entireRow.Insert?
– BruceWayne
Nov 22 at 5:47
There's no need to select row before deleting (in general, avoid selections). What is the row number ofTarget.Row?
– JohnyL
Nov 22 at 8:54
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside the
Worksheet_BeforeDoubleClick() event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.– K.Dᴀᴠɪs
Nov 22 at 4:02
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside the
Worksheet_BeforeDoubleClick() event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.– K.Dᴀᴠɪs
Nov 22 at 4:02
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
Try just
Rows(target.row + 1).entireRow.Insert?– BruceWayne
Nov 22 at 5:47
Try just
Rows(target.row + 1).entireRow.Insert?– BruceWayne
Nov 22 at 5:47
There's no need to select row before deleting (in general, avoid selections). What is the row number of
Target.Row?– JohnyL
Nov 22 at 8:54
There's no need to select row before deleting (in general, avoid selections). What is the row number of
Target.Row?– JohnyL
Nov 22 at 8:54
|
show 4 more comments
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423285%2fmethod-insert-of-object-range-failed-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423285%2fmethod-insert-of-object-range-failed-error%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try adding Application.EnableEvents = False at the beginning, and Application.EnableEvents = True at the end. Don't forget to set Cancel=True in both conditions under which you take action. If this helps, I'll write a formal answer, as there are a couple gotchas.
– Excelosaurus
Nov 22 at 3:20
I have ran this several times without issue. There's something else going on here - anything else you can think of that may help? Do you have any other events beside the
Worksheet_BeforeDoubleClick()event? I mean, there are definitely a few things in your code I would optimize, but nothing that would throw your particular error.– K.Dᴀᴠɪs
Nov 22 at 4:02
I'll try adding the Application.Enable Events = False, thnks! I do have security on the sheet as you can tell by the code. The only other thing is that I have some data validation and some conditional formatting, but even when I only put a bit of text in the cells not affected by these things I'm getting the error.
– Nic
Nov 22 at 5:11
Try just
Rows(target.row + 1).entireRow.Insert?– BruceWayne
Nov 22 at 5:47
There's no need to select row before deleting (in general, avoid selections). What is the row number of
Target.Row?– JohnyL
Nov 22 at 8:54