Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
In the Excel 2007 VBA Programmer's Reference (by John Green, Stephen Bullen,
Rob Bovey and Michael Alexander), they state: ----- "As well as being able to retrieve the state information, you can also click any built-in control using the new ComandBars.ExecuteMso method. This is extremely useful for triggering those actions that dont have an object model equivalent€”such as putting the user in drawing mode to draw a text box on a sheet:" Application.CommandBars.ExecuteMso €śTextBoxInsertExcel€ť ----- While the above works, when I try to use this method with a gallery control, like: Application.CommandBars.ExecuteMso "CellStylesGallery" I expect either the book is wrong (i.e., the method does not apply to "any built-in control"), or there is some syntax I have wrong in applying this. Can anyone advise how to make this work? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Jeffery
Not quite got what you are seeing here, but to get the Mso of any control go to Office ButtonExcel OptionsCustomize and change the drop down to 'All Commands', find the control in the list, hover over it until the tooltip appears and the 'Mso' is the name in brackets e.g. (TextBoxInsertText), you can then try using that in your VBA after the ExecuteMso statement. -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England DTHIS web: www.nickhodge.co.uk "JeffreyW" wrote in message ... In the Excel 2007 VBA Programmer's Reference (by John Green, Stephen Bullen, Rob Bovey and Michael Alexander), they state: ----- "As well as being able to retrieve the state information, you can also click any built-in control using the new ComandBars.ExecuteMso method. This is extremely useful for triggering those actions that dont have an object model equivalent€”such as putting the user in drawing mode to draw a text box on a sheet:" Application.CommandBars.ExecuteMso €śTextBoxInsertExcel€ť ----- While the above works, when I try to use this method with a gallery control, like: Application.CommandBars.ExecuteMso "CellStylesGallery" I expect either the book is wrong (i.e., the method does not apply to "any built-in control"), or there is some syntax I have wrong in applying this. Can anyone advise how to make this work? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Hi JeffreyW
I think you are right and it is not working for a gallery. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JeffreyW" wrote in message ... In the Excel 2007 VBA Programmer's Reference (by John Green, Stephen Bullen, Rob Bovey and Michael Alexander), they state: ----- "As well as being able to retrieve the state information, you can also click any built-in control using the new ComandBars.ExecuteMso method. This is extremely useful for triggering those actions that dont have an object model equivalent€”such as putting the user in drawing mode to draw a text box on a sheet:" Application.CommandBars.ExecuteMso €śTextBoxInsertExcel€ť ----- While the above works, when I try to use this method with a gallery control, like: Application.CommandBars.ExecuteMso "CellStylesGallery" I expect either the book is wrong (i.e., the method does not apply to "any built-in control"), or there is some syntax I have wrong in applying this. Can anyone advise how to make this work? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Nick,
I do have the correct mso label in the following statement: Application.CommandBars.ExecuteMso "CellStylesGallery" Yet it fails with a "Run-time error '5': Invalid procedure call or argument". I think the book is wrong - in fact, looking at Help more closely now, I see that it says: "Works on controls that are built-in buttons, toggleButtons and splitButtons." So, by definition, I guess that excludes Gallery buttons. Jeff |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Hi Ron,
Yeah, I think you are right. As indicated in my reply to Nick's post above, Help indicates that the method only "Works on controls that are built-in buttons, toggleButtons and splitButtons." Jeff |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Ron, I was wondering if the method you suggested on another of my questions
recently: Application.CommandBars.FindControl(ID:=23).Execut e .... might work here. I doubt it will given the failure of the .ExecuteMso method but thought it was worth asking ... How do I find the "ID" for the "CellStylesGallery" function to try this? I tried looking at your "Built-in controls in Excel 2007.xlsm" file, but the "Ordering" and "Policy ID" listed there did not work with the FindControl method. Both generated errors: "Run-time error '91': Object variable or With block variable not set" Jeff |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
This is only working for the old 2003 controls Jeffrey.
No way to do what you want on this moment as far as I know -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JeffreyW" wrote in message ... Ron, I was wondering if the method you suggested on another of my questions recently: Application.CommandBars.FindControl(ID:=23).Execut e ... might work here. I doubt it will given the failure of the .ExecuteMso method but thought it was worth asking ... How do I find the "ID" for the "CellStylesGallery" function to try this? I tried looking at your "Built-in controls in Excel 2007.xlsm" file, but the "Ordering" and "Policy ID" listed there did not work with the FindControl method. Both generated errors: "Run-time error '91': Object variable or With block variable not set" Jeff |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making Application.CommandBars.ExecuteMso method work in 2007
Ok - thanks, Ron.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Making touchpad's Horizontal Scroll region work in Excel/Word 2007 | Excel Discussion (Misc queries) | |||
Excel 2007 - Personnal CommandBars | Excel Programming | |||
GetObject method not work after Call Shell Method | Excel Programming | |||
Why QUIT method doesn't work after COPY method? | Excel Programming | |||
Making VB PasteSpecial method to work the same as Excels Application | Excel Programming |