+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Registered User
    Join Date
    10-05-2007
    Posts
    5

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

    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:

    Please Login or Register  to view this content.
    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.
    Last edited by VBA Noob; 10-05-2007 at 02:52 AM.

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    You've posted in the correct forum, but you need to edit your post to include code tags:

    [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE]

  3. #3
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678
    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).

  4. #4
    Registered User
    Join Date
    10-05-2007
    Posts
    5

    Thanks SHG but...

    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

    Quote 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).

  5. #5
    Registered User
    Join Date
    10-05-2007
    Posts
    5

    Ignore my reply SHG - I've worked it out

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1