Runtime Error 1004 when viewing an Excel Dialog box (Excel 2007)

[Jack21]

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

[HansV]

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!)

[Jack21]

Many thanks Hans.