Solved

Creating a menu for macros in Excel 2003

Posted on 2011-02-17
8
289 Views
Last Modified: 2012-05-11
I have used the embedded macro menu to organize some macros - this only works only in 2007, how can I get it to work in 2003?  I am open to using another menu system to organize the macros.
Thanks,
JP My-Add-in-Macro-Menu-all-workboo.xlam
0
Comment
Question by:easycapital
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34915693
Dear Easycapital, I use it like this in Excel 2003.

 


Private Sub Workbook_Open()
 With Application
         .Calculation = xlAutomatic
        .MaxChange = 0.001
 '  End With
  DeleteMenuItem
 
  With Application.CommandBars(1).FindControl(, ToolsMenuID) _
  '   .Controls.Add(msoControlButton)
    .Caption = MenuCaption
 .OnAction = ThisWorkbook.Name & "!" & MacroName
  'End With
End Sub

Private Sub DeleteMenuItem()
  Dim ToolsMenu As CommandBarControl
 
  On Error Resume Next
  Application.CommandBars(1).FindControl(, ToolsMenuID) _
    .Controls(MenuCaption).Delete
  On Error GoTo 0
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 ' DeleteMenuItem
 On Error Resume Next
 Application.ActiveWorkbook.Save
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'Call UnHookMouse
'nonoH = True
End Sub
0
 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34915719
or create a manu like this
Sub CreateMenu()
'   This sub should be executed when the workbook is opened.
'   NOTE: There is no error handling in this subroutine

    Dim MenuSheet As Worksheet
    Dim MenuObject As CommandBarPopup

    Dim MenuItem As Object
    Dim SubMenuItem As CommandBarButton
    Dim Row As Integer
    Dim MenuLevel, NextLevel, PositionOrMacro, Caption, Divider, FaceId

''''''''''''''''''''''''''''''''''''''''''''''''''''
'   Location for menu data
    Set MenuSheet = ThisWorkbook.Sheets("MenuSheet")
''''''''''''''''''''''''''''''''''''''''''''''''''''

'   Make sure the menus aren't duplicated
    Call DeleteMenu
   
'   Initialize the row counter
    Row = 2

'   Add the menus, menu items and submenu items using
'   data stored on MenuSheet
   
    Do Until IsEmpty(MenuSheet.Cells(Row, 1))
        With MenuSheet
            MenuLevel = .Cells(Row, 1)
            Caption = .Cells(Row, 2)
            PositionOrMacro = .Cells(Row, 3)
            Divider = .Cells(Row, 4)
            FaceId = .Cells(Row, 5)
            NextLevel = .Cells(Row + 1, 1)
        End With
       
        Select Case MenuLevel
            Case 1 ' A Menu
'              Add the top-level menu to the Worksheet CommandBar
                Set MenuObject = Application.CommandBars("Formatting"). _
                    Controls.Add(Type:=msoControlPopup, _
                    Before:=PositionOrMacro, _
                    Temporary:=True)
                MenuObject.Caption = Caption
           
            Case 2 ' A Menu Item
                If NextLevel = 3 Then
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlPopup)
                Else
                    Set MenuItem = MenuObject.Controls.Add(Type:=msoControlButton)
                    MenuItem.OnAction = PositionOrMacro
                End If
                MenuItem.Caption = Caption
                If FaceId <> "" Then MenuItem.FaceId = FaceId
                If Divider Then MenuItem.BeginGroup = True
           
            Case 3 ' A SubMenu Item
                Set SubMenuItem = MenuItem.Controls.Add(Type:=msoControlButton)
                SubMenuItem.Caption = Caption
                SubMenuItem.OnAction = PositionOrMacro
                If FaceId <> "" Then SubMenuItem.FaceId = FaceId
                If Divider Then SubMenuItem.BeginGroup = True
        End Select
        Row = Row + 1
    Loop
End Sub
0
 

Author Comment

by:easycapital
ID: 34916679
Zwiekhorst: Thank you for sharing.  I was not able to run them. I placed them under "Module" in the VBA editor section, but I was not able to set it up right.

Could you either embed a file with the suggested code, or take a look at the two files I created with your code?
Thanks,
JP Menu-1-approach.xls Menu-2-approach.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Expert Comment

by:Eric Zwiekhorst
ID: 34916911
You can umbed them in the workbook macro

please look at the file that I upload

Menu-1-approach-1-.xls
0
 

Author Comment

by:easycapital
ID: 34920645
Zwiekhorst:
I want to apologize in advance.  What does the macro do?  I open the file in 2003 and nothing happens.  Could you explain me a little more?
Thanks,
JP
0
 
LVL 6

Accepted Solution

by:
Eric Zwiekhorst earned 250 total points
ID: 34924090
The macro will run when opening the workbook.
It will create a menu sheet with all possible icons for the button.

Hope it will help you out.

KR

Eric
Menu-opbouw.xls
0
 
LVL 4

Assisted Solution

by:yuppydu
yuppydu earned 250 total points
ID: 34924712
Here you go!

Tested and it works.

Hope is what you're looking for.
personal-menu.xls
0
 

Author Closing Comment

by:easycapital
ID: 34932465
Real nice!
Eric and yuppydu - Thanks.
Yuppydu - I am using your download right now.
JP
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question