Solved: Macros men?

[sasa]

I have many macros and every time to start these, I have to open the usual excel drop down windows many times. Is there a way to have all the macros in a unique menu
on my excel bar in a stable and polite way ?
I have tryed many times but the results are ever poor.

Sincerely

Sasa

[Ken Puls]

Hi Sasa,

Are you using Excel 2003 or lower, or Excel 2007?

If you are using any version of Excel from 97 through 2003, try John Walkenbach's menu maker. You can find it here (http://www.j-walk.com/ss/excel/tips/tip53.htm). It makes it really easy to create a menu in those versions of Excel.

If you are using Excel 2007, you'll need to write some XML as well as some VBA. (I can help you there if you are using that version.)

HTH,

[sasa]

I am using Excel 2007 but if the problem is too complex for me I can dowmgrade to Excel 2003

[Ago]

i use this way:

Sub AddItemToContextMenu()
Dim cmdNew As CommandBarButton

Set cmdNew = CommandBars("cell").Controls.Add
With cmdNew
.Caption = "ImportQConsole" ' this is the name that will appear
.OnAction = "ImportQConsole" ' this is the macro name
.BeginGroup = True 'notice the true here compared to the false in the next one.
End With

Set cmdNew = CommandBars("cell").Controls.Add
With cmdNew
.Caption = "Dates" ' this is the name that will appear
.OnAction = "Dates" ' this is the macro name
.BeginGroup = False
End With
End Sub


and to remove it again:
Sub RemoveContextMenuItem()
On Error Resume Next
CommandBars("cell").Controls("ImportQConsole").Delete ' <-- Change to your macro
CommandBars("cell").Controls("Dates").Delete ' <-- Change to your macro
End Sub


it will give you shortcuts when you rightclick in a cell

[Ken Puls]

Hi Sasa,

Okay, I'll tell you what. I can get the XML built for you, if you can get me some information...

I need a table from you (in an Excel sheet is probably easiest) that lists:
Description you want on menu --> Macro name --> imageMso name

You can indentify your imageMso names by downloading the Office 2007 icons gallery add-in from my blog (http://www.excelguru.ca/blog/2007/05/05/identifying-your-imagemso-excel-word-powerpoint/).

If you're interested in giving this a go yourself, you can also find a bunch of info on working with XML on the RibbonX table of contents on my blog (http://www.excelguru.ca/blog/2006/12/01/ribbon-example-table-of-contents/), or in my new book RibbonX - Customizing the Office 2007 Ribbon (http://www.excelguru.ca/node/93). (Chapter 9 is all about menus.)

[sasa]

Well, after a long web search I found this solution. It works and the macro men? easily jumps on my excel 2007 toolbar. This is the link:
http://www.contextures.com/xlToolbar02.html.

In your opinion is there the way to improve the look of the macro men?, for example the way it may looks like a pull down men? ?

Thanks in advance

Sasa

[Ken Puls]

Hi Sasa,

Sorry, but if you want this to look better in 2007, you're pretty much going to need to work with the Ribbon (and XML). The toolbar method will work to create functionality, but you're going to be stuck with it's implementation in the Add-ins tab, as well as the limited look. You can create pull down menus, to be certain, but you are going to get much better control in Excel 2007 by dealing with the XML aspects of the Ribbon.

Again, if you want to give me a table, as I listed in a post above, I can help you build the XML for your menus.

[sasa]

Description you want on menu --> Macro name --> imageMso name
Days-->Giorni-->ViewAllProposals
Names-->Nomi-->AddOrRemoveAttendees
Acronyms-->Sigle-->DiagramReverseClassic
Points-->Punti-->SmartArtChangeColorsGallery
Others-->Altri-->MeetingsWorkspace
I don't know if I well understood , anyway this is my first attempt. One of my three worksheets.
But for example it is not clear to me if this menus can be stay there in every worksheet I am going to open and if it is necessary thath all the macros have to stay there all together.

thanks for your attention

sasa

[Ken Puls]

Hi Sasa,

I've attached a file for you that works as a dynamic menu creator, and works in a similar manner to John Walkenbach's 97-2003 MenuMaker. I've filled in your items in it, but you'll need to drop your macros into the modProcedures module.

Some limitations to this are the we can't dynamically move the menu to a different tab, or different position on the tab. If you'd like it in a different place the XML will need to be modified, which I can either help you do, or take care of for you. Just let me know.

Hope it helps,

[Ken Puls]

But for example it is not clear to me if this menus can be stay there in every worksheet I am going to open and if it is necessary thath all the macros have to stay there all together.

Sorry, forgot to answer this part...

The menu will stay on the tab for every worksheet in the workbook. (Provided that you use my file, as it contains the XML. This XML code MUST be in your workbook.)

The alternative is to save this file as an add-in, and distribute it to all users who need to use the functionality. At that point it is available to all workbooks, and all worksheets within them.

[sasa]

Thank you, it is a good solution.

Thanks again