Method 'Insert' of object 'Range' failed error

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.

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

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 '18 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 '18 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 '18 at 5:11

Try just Rows(target.row + 1).entireRow.Insert?

– BruceWayne
Nov 22 '18 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 '18 at 8:54

|
show 4 more comments

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"

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.

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

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 '18 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 '18 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 '18 at 5:11

Try just Rows(target.row + 1).entireRow.Insert?

– BruceWayne
Nov 22 '18 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 '18 at 8:54

|
show 4 more comments

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"

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.

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

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"

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 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

edited Nov 22 '18 at 3:57

K.Dᴀᴠɪs

7,051112339

asked Nov 22 '18 at 3:04

Nic

asked Nov 22 '18 at 3:04

Nic

asked Nov 22 '18 at 3:04

Nic

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 '18 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 '18 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 '18 at 5:11

Try just Rows(target.row + 1).entireRow.Insert?

– BruceWayne
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 5:11

Try just Rows(target.row + 1).entireRow.Insert?

– BruceWayne
Nov 22 '18 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 '18 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 '18 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 '18 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 '18 at 5:11

Try just Rows(target.row + 1).entireRow.Insert?

– BruceWayne
Nov 22 '18 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 '18 at 8:54

|
show 4 more comments

0

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',
autoActivateHeartbeat: false,
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
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

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

Name

Required, but never shown

0

active

oldest

votes

0

active

oldest

votes

draft saved

draft discarded

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.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Tukukkk