Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi

    I've been forwarded a piece of code to troubleshoot but am getting no-where fast! It appears that when a shape (triangle, star etc) is inserted on a worksheet in Excel 2007, the shape is then selected, the below code throws an Runtime 1004 error and does not allow the dialog box to open:

    Application.Dialogs(xlDialogPatterns).Show

    This is not the case when selecting a basic rectangle or square in the same way. If the code is run outside of the shape it works as expected.

    Can anyone enlighten me on why this seems to work for some shapes but not others?

    Many thanks in advance.
    Jack

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    xlDialogPatterns shouldn't work for AutoShapes at all, so I think it's a bug that it works for rectangles.

    There is no xlDialog constant for the Format AutoShape dialog, but you can use

    SendKeys "^1", True

    to evoke it. (See XL97: Macro Error Using Show Method with AutoShape Selected - as you see the problem is much older than Excel 2007!)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2005
    Location
    Kent, England
    Posts
    138
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks Hans.

Posting Permissions

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