VBA error "Method 'Select' of object 'Shape' failed"

**cvillarl** · 03-03-2012, 08:41 PM

Hello everybody,

I'm having some Excel VBA troubles, and after no luck finding a solution, I've decided to register and post a question. So first of all; "hello"

I'm working on a university project (using Excel 2010 in a Windows 7 Home Premium SP1) and I get the not very helpful error:

"Microsoft Visual Basic for Applications
System Error &H80004005 (-2147467259)."

Trying to identify the source of the problem, I've tried on a different computer (using Excel 2007 in a Windows 7 Enterprise SP1) and I get the slightly more helpful error:

"Microsoft Visual Basic
Run-time error '-2147467259 (80004005)':
Method 'Select' of object 'Shape' failed"

The error appears when I run the procedure that I've copied below. I've highlighted the line that Excel 2007 identifies as the source of the problem. The worksheet is currently unprotected while I work on it, that's the reason why the code to unprotect and protect it is commented out.
I have a table with similar information and the same form controls in the different rows. The user selects the row above which they want to insert a blank row and they call the procedure by clicking a form control. The puzzling thing is that most of the times it works fine, but if the user tries to insert a blank row above certain rows (and always the same rows) they get this errors. I'm at lost. Thank you for any suggestions as to what the solution can be.

Please Login or Register  to view this content.

**Simon Lloyd** · 03-04-2012, 04:12 AM

Firstly instead of selecting like this

Please Login or Register  to view this content.

use this

Please Login or Register  to view this content.

and see where it gets you, failing that why not post a sample workbook with ALL the code in so we can help further?

Google Me

Find me located here Simon Lloyd and what i'm about Here
The above is NOT a link to a forum so is NOT against Rule 13

**cvillarl** · 03-04-2012, 03:17 PM

Hello Simon,

Thank you for your suggestions. I've first tried your alternative, however, I get the following error message in Office 2010:

"Microsoft Visual Basic for Applications
Object doesn't support this property or method"

I haven't tried in Office 2007, I'll try tomorrow when I go to uni.
I've edited the spreadsheet to remove the confidential information before sharing it here, and to my amusement I've found that after editing it, the procedure works! I've repeated all the edition steps, testing the procedure after each of them, and I've found that the procedure works if I remove the comments. Please find the spreadsheet attached (with the comments left but edited).
To reproduce the problem, go to the tab "Teaching", select the row 30 (or any row with comments), and click in "Add Module" (top left button).
My theory is that the comments are also considered a kind of "shape" that is accidentally caught in the loop intended only for form controls:

Please Login or Register  to view this content.

This causes the error as comments don't have a "LinkedCell". I guess that I would have add a filter within the loop that ignores the comments. Am I in the right direction? Thank you.

**Kyle123** · 03-04-2012, 04:12 PM

For just checkboxes and dropdowns:

Please Login or Register  to view this content.

**cvillarl** · 03-04-2012, 08:41 PM

Hello Simon and Kyle,

It was the comments, as I suspected. I've used the code suggested by Kyle and now it seems to be working fine. Thank your for your help.