Error message - Method 'Select' of object 'Shape' failed

**cdchi1** · 10-05-2007, 01:45 AM

Hi all

I am having what to me is a very strange problem since I've never had this issue before.

Basically I have a calculator I've developed in Excel. This has some simple 'sheet switching' macro buttons.

But for some reason, many of these buttons will not work (I get a runtime error) when the worksheet they are on is protected. If I unprotect the worksheets they work fine. The strange thing is that the other macros, and one 'sheet switching' macro work fine.

The stranger thing is that the coding I have used is identical to that used for previous calculators.

The following is the runtime error I get when selecting the macro button while the worksheet is protected:

Run-time error '-2147467259 (80004005)':

Method 'Select' of object 'Shape' failed

The following is an example of one of the coding for one of the buttons:

Sub Accept()
'
' Accept Macro
' Macro recorded 26/09/2006 by Bscxc
'

'
    Sheet1.Unprotect Password:="carlo"
    ActiveSheet.Shapes("Button 21").Select
    Selection.Characters.Text = "Accept"
    With Selection.Characters(Start:=1, Length:=6).Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
    Sheets("Projector").Select
    Sheet1.Protect Password:="carlo"
    
End Sub

Any thoughts?

Cdchi1

Ps: Since this is my first post on this forum apologies in advance if I've submitted this to the wrong forum topic.

**shg** · 10-05-2007, 02:03 AM

You've posted in the correct forum, but you need to edit your post to include code tags:

[CODE]your code here[/CODE]

**shg** · 10-05-2007, 02:10 AM

When a sheet is protected, you can't manipulate shapes unless the shape is unlocked (which you can do via the user interface - Format > AutoShape, or Format > Control for buttons from the Forms toolbar), or the sheet is protected with the UserInterfaceOnly option (which you can look up in VBA Help).

**cdchi1** · 10-05-2007, 02:48 AM

Thanks for the quick reply SHG, and I'll remember that bit about using <code> going forward.

Your suggestion worked in terms of allowing me to use the macro with the worksheet protected by 'unprotecting' the button. Thats great!

However this allows another user to edit the button. I need to adjust it so that it works while the macro button is protected as well, as per the other macro buttons that works as intended.

As can be seen from the macro coding, the macro is 'unprotecting' the sheet so that even if the control box is locked it should be selectable without any problem. The macro then reprotects the sheet after its run. Why its giving that error for this calculator remains a mystery as it has never happened before.

Cdchi1

Originally Posted by shg

When a sheet is protected, you can't manipulate shapes unless the shape is unlocked (which you can do via the user interface - Format > AutoShape, or Format > Control for buttons from the Forms toolbar), or the sheet is protected with the UserInterfaceOnly option (which you can look up in VBA Help).

**cdchi1** · 10-05-2007, 03:00 AM

Amazingly within 5 minutes after I replied!!!

The issue was with an OFFSET command in another macro that would only allow macro navigation from a particular sheet (the one with the working macro).

Very strange and not sure why that would be the case, but when i removed that OFFSET command everything worked fine.

Cdchi1