VBA code working but giving "run-time error '-2147352571 (80020005)'"

[ABech1]

Hello

As so many others in here i'm a rookie at VBA trying to make some nice QOL improvements at my new job.

Hence i tried to add a button that would extend a small table (not formatted as table though) and keep the formatting and formulas. And I actually succeded (atleast a little bit) and the button works as intended. Therefore it was a surprise to me, that when i run the code in the VBA editor, it gives me "run-time error '-2147352571 (80020005)'"

As you can probably understand i do not like this, and would like to solve it before shipping the spreadsheet out to the intended users.

My code is as follows:

Sub Tilføj_række_Hovedark()
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Shapes(Application.Caller)
    With b.TopLeftCell.EntireRow.Offset(-2, 0)
.Insert
.Cells(1, 2).Copy .Cells(0, 2)
.Cells(1, 3).Copy .Cells(0, 3)
.Cells(1, 4).Copy .Cells(0, 4)

    End With
End Sub

And the table looks like this. The object used as trigger is just an icon inserted from the excel-icons.

here's a pic of the debug result:

I really can't seem to figure out, what the problem is.

Please help me

Thanks in advance

 

[GWteB]

... when i run the code in the VBA editor, it gives me "run-time error '-2147352571 (80020005)'

Your attempt directly running the code in the VBE leads to returning an error by the Application.Caller property. Since the returned error is not a valid index for the Shapes collection, a run-time error such as you experienced occurs. In this particular case you better set a breakpoint on the top line of your Tilføj_række_Hovedark() procedure using F9 key in the VBE. That way you're able to step through the code for testing purposes when the button which this macro is assigned to is pressed.

 

[ABech1]

Hello. And thanks for your response.

I'm sorry but i cant seem to figure out, how that will help me. I tried setting a breakpoint at the top line (and everywhere else in the code) and used F5 and F8 to run through the code, but i allways end up with the same result. Whenever i get to my third line:

Set b = ActiveSheet.Shapes(Application.Caller)

i get the same error as described.

What i just dont understand is, why i get the error, when my button actually does what i wants it to do.

 

[Marc L]

Hello,​

​

as this codeline can't be executed in debug mode as yet explained,​

if you need to debug your procedure so just replace Application.Caller or via a breakpoint manually jump to the next codeline …​

​

As a reminder a good code using Caller must first test if it returns an error before to use it.​

 

[RoryA]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Working code returns run-time error '-2147352571 (80020005)'
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[ABech1]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Working code returns run-time error '-2147352571 (80020005)'
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

Oh... I thought i remembered everything, but this one slipped... I wil edit on both forums ASAP

 

[ABech1]

Hello,​

​

as this codeline can't be executed in debug mode as yet explained,​

if you need to debug your procedure so just replace Application.Caller or via a breakpoint manually jump to the next codeline …​

​

As a reminder a good code using Caller must first test if it returns an error before to use it.​

So if i understand you correctly, there might not be a problem in the code? It's just a matter of not being able to execute Application.caller in debug mode?

 

[Fluff]

If you click the icon, does your code work?

 

[GWteB]

So if i understand you correctly, there might not be a problem in the code? It's just a matter of not being able to execute Application.caller in debug mode?

Now you've got it.

 

[ABech1]

If you click the icon, does your code work?

Yes. It works as intended. That's why I was surprised to se the "bug" in VBE.