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.

Sub add_module()
    'Adds a new row for a new module
    Dim num_rows
    Dim sel_row
    Dim index
    Dim column
    '### It unprotects the worksheet
    'Worksheets("Teaching").Unprotect
    'It deactivates screen updating
    Application.ScreenUpdating = False
    num_rows = count_rows("Teaching", modules_table, 1)
    If Not TypeName(Selection) = "Range" Then
        MsgBox ("First select the row where the new module will be added (within rows " & modules_table + 2 & "-" & modules_table + num_rows & ").")
    ElseIf Not Selection.Rows.Count = 1 Then
        MsgBox ("First select the row where the new module will be added (within rows " & modules_table + 2 & "-" & modules_table + num_rows & ").")
    ElseIf Selection.row < modules_table + 2 Or Selection.row > modules_table + num_rows Then
        MsgBox ("First select the row where the new module will be added (within rows " & modules_table + 2 & "-" & modules_table + num_rows & ").")
    Else
        sel_row = Selection.row
        'Copies the row
        Worksheets("Blank").Range("1:1").Copy
        Worksheets("Teaching").Rows(sel_row).Insert
        'Updates the controls
        For index = ActiveSheet.Shapes.Count To 1 Step -1
            If ActiveSheet.Shapes(index).TopLeftCell.row = sel_row Then
                column = ActiveSheet.Shapes(index).TopLeftCell.column

                'Source of the problem

                ActiveSheet.Shapes(index).Select

                'Source of the problem

                Selection.LinkedCell = number_letter(column) & sel_row
            End If
        Next
    End If
    '### It protects the worksheet
    'Worksheets("Teaching").Protect
    'It reactivates screen updating
    Application.ScreenUpdating = True
End Sub

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

Firstly instead of selecting like this

ActiveSheet.Shapes(index).Select

Selection.LinkedCell = number_letter(column) & sel_row

use this

ActiveSheet.Shapes(index).LinkedCell = number_letter(column) & sel_row

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:

For index = ActiveSheet.Shapes.Count To 1 Step -1
'...
Next

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:

Dim shp As Shape
Dim dbType As Double
For Each shp In Sheets("teaching").Shapes
    dbType = VarType(shp.OLEFormat.Object)
    If (dbType = 3 Or dbType = 5) Then
        
    End If
Next shp

**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.