Macro Code To Create Custom Menus & Sub Menus

Jun 9, 2008

I am using the following code to create a custom command menu.

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
On Error Goto 0
Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")..........................

I want to add a new button control at the bottom of the menu underneath the holiday control but it keeps adding it on the sub menu, which leads off the holiday button control. I want the menu to look like this.

Open Net 2 Access
Add Employee
Edit Employee
Delete Employee
Holidays (3 options on sub menu)
*New Control

View 4 Replies


ADVERTISEMENT

Custom Menus And Sub Menus For 2007

Feb 3, 2010

From this link: [url]

there is some clever code to create a customized menu in a dynamic Add-Ins section of the Excel ribbon; this section disappears when the workbook is deactivated and re-appears when it is activated. The custom menu has 2 main items each of which can launch a macro. There is also a 3rd main item called "Next Menu" which cascades nicely to a sub-menu. That sub-menu can have several items of its own. Very cool code.

However, I am trying to add a 4th item in the main menu, say, "Next Menu2" which also cascades to a sub-menu. The problem I'm having is when I try to replicate this code to accomplish this, "Next Menu2" appears in the sub-menu of "Next Menu", rather than as a separate (4th) main item below "Next Menu".

I believe there was another post (not sure of the date, but has since expired) which asked basically the same question as mine. There was a reply with code saying "This should work" but when I tried it, it didn't help.

View 3 Replies View Related

Create Custom Menus

Apr 19, 2007

I was reviewing the "Create Custom Menu Items in Excel VBA" code located at [url] and cannot figure out something. How do I add a menu dropdown that contains MORE than one submenu item? This is the code my question pertains to: ...

View 9 Replies View Related

Create Custom Menus For Specific File

Sep 14, 2007

I tried to create a custom menu for a specific file. However, after creating the menu, I posted the file in Sharepoint. When a user download the file from the sharepoint site, the custom menu doesn't work because it is looking for the macro links from my computer. Another problem is that now the custom menu shows up in all other excel files that I open.

My questions are:
1. How do I do it so that the custom menu shows up only when this file is opened up.
2. How do I go about making the menu to look for the macros embeded in the file itself instead of looking for it in my file folder.

View 9 Replies View Related

Create Toolbar Menus & Sub Menus

Oct 4, 2007

I want to create a submenu to an already existing user defined menu.

After creating and saving the sub menu using Tools ->Customize ,it works fine. But when

I reopen the Excel sheet it is not found.

I could not find any macro related to the original User menu.Also this menu gets displayed after the AUTO OPen macro in my excel has worked. This menu gets displayed for only that workbook and not for others.

I have been trying to find out a way to create Sub menu for that ,but could not?

View 3 Replies View Related

Custom Context Menus For Any WB (using Add-in?)

Jan 12, 2010

Ok, so I have this neat little macro that on right-click brings up a custom context menu that displays a few different options - for example one such function is simply copying the selected cell content to a (specific) different sheet. It is a very useful feature in the context I am normally using it to merge information from different locations in a new structure (well, I think this is irrelevant, but just letting you know basically what I am doing).

Anyhow, to implement the custom context menu, I am using events in the ThisWorkbook object, namely Activate to add context menu entries and DeActivate to remove them if switching workbook.

Now, if I receive any workbook from someone else, I'd like to add this feature to get the custom context menu easily without having to copy the received workbook into a new workbook (it's a bit of a hassle, and ideally, if the received workbook has any other macros, this function could just be merged). I was thinking using an add-in to achieve this, but using the ThisWorkbook events in the add-in doesn't trigger the events (I guess because the hidden workbook in the add-in is never "Activated" or "DeActivated").

View 6 Replies View Related

Custom Menus Available To One Workbook

Sep 24, 2008

There was an article in ozgrid new about excel custom toolbars (<<hover over). The article addressed making the custom menu option available in one workbook with Private Sub Worksheet modules as follows

Private Sub Worksheet_Activate()
Application.CommandBars("Worksheet Menu Bar").Controls _
("My Menu").Enabled = True
End Sub

Private Sub Worksheet_Deactivate()
Application.CommandBars("Worksheet Menu Bar").Controls _
("My Menu").Enabled = False
End Sub

How do you get the first module to run apon opening the workbook and the second module to run when you close the workbook.

View 7 Replies View Related

Custom Menus 2000 Vs 2007

Feb 6, 2009

We are currently on Office 2000, but our IT group are considering a company wide switch to 2007. I've played around with it just briefly, but noticed that it is night and day difference in both look and feel.

We use Hyperion Essbase, and have a custome built planning tool that utilizes Excel 2000, enhaced with a few thousand lines of VBA coding. One of the main purposes of the VBA code, is to control the Excel menus and replace with custom built menus that are specific to our Planning System.

Does anyone have any idea if VBA controlled custom menus from Excel 2000 will even work in 2007? Will I have to do complete rewrite?

One last question, If you are running 2007, is there a transition function (simlar) to prior versions, where you make the menus look like the legacy version?

View 10 Replies View Related

Sharing Custom Menus & Their Macros

Aug 26, 2008

Our ERP system generates reports in CSV. I have created a custom excel menu, which houses a lot of buttons that launch various macros to format these reports. I have been asked to share these formatting macros with other users.

I understand that the custom menu structures are stored in an excel11.xlb file. My thought was to simply replace the users *.xlb file. This only half worked. I get the menus and the macros, but when the users try to run the macros from the menu excel can't find them because the path is pointing to my user name.

View 3 Replies View Related

Add Custom Menus To Single Workbook Only

Apr 3, 2008

Using example code from Adding Custom Menus, I now have functional menu items that call my macros appropriately. The example also contains code to add or remove a menu conditional on its window being active. What I need is a slight variant on this but not quite obvious to my novice brain.

The add and remove menu code is now stored in my personal.xls file. However, I need that the condition check that a key string be present in the name of the active window, not the file where the macro is stored.

Thus, if the front window is of the file named MyData, then the special menus get added (the conditional string is "MyData"). If the window is named YourData, the special menus get removed. Note, neither of these two files contain any macros.

View 4 Replies View Related

Custom Ribbon Menus Don't Work Until Save, Close & Re-Open

Feb 15, 2010

Created a new tab and menu buttons on the Ribbon in Excel in 3 separate .xltm files. Everything worked great through all of the development and testing, but when we went live we got reports of a bug. Here is what is happening...

User opens the template file and immediately saves it as a .xlsm file with a new name in a new location. The user then does various amounts of work in the file. When the user needs to use one of the tools on the custom tab, it is as if the buttons are inactive or not associated with any actions. The tab and buttons are all visible, the animation of the button being pressed works when any of the buttons are clicked, but nothing happens. The user can close and reopen the file and the ribbon buttons will work correctly, but not until they close and reopen.

Upon digging into the problem, we found that if the user clicks on the custom tab before they save the file, all of the buttons will work correctly after the save. But if the tab is not activated before the save, they dont work until a close and reopen.

The XML code is good because if I place it in a add on file (.xlam) and trigger it from the main file, there is no problem. But this is not a good solution since the add on file needs to be located in the same file that the main file is run from which can be any number of places.

View 3 Replies View Related

Create Dynamic Dependent Dropdown Menus

Mar 11, 2013

I need the drop down menu to update when the user adds a new entry. For example, if the user decides to add companies to the financial table, no matter how many new rows they add, our drop down menu at the top will update to include the new the entries. I want this to be the case in every table. How do I do this?

View 5 Replies View Related

Indirect Function And Substitute In Order To Create Conditional Dropdown Menus

Jun 26, 2014

I work for a Machine Shop in the Toledo area. We use a quotation sheet to quote our products to our customers. It has 3 cells that describe the Customer, the Contact person at that customer and finally that Contact's Email Address. I have a drop-down menu in each of these cells. The first, Customer, is based off a simple list of our customers. The Contact drop-down then uses the Indirect Function to search the worksheet for that Customer. The drop down is actually based off of a Range Name saved as that Customer's Name.

However the problem is many of my customers have & or , and since Excel does not allow those symbols or even spaces in a Range Name I have to use Substitute to be able to keep the spaces and the & or ,.

It is important that on the Quote Sheet the Name of the Customer is identical to the actual name of the company so I cant use AND instead of &.

Currently my Second drop down, Contacts, has a formula that looks like this {=INDIRECT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($H5,"_"," "), "3", "&"), "2",","))}.

My problem is that when I give the Range Name a Name , my drop down doesn't work and Excel tells me that the above formula results in an error.

View 6 Replies View Related

Excel 2007 :: Make Button In Ribbon Menus To Run Macro?

Aug 9, 2013

I've been trying to research how to do this for a while now and still can't figure it out. My impression is that this is fairly straightforward in Excel 2010 since the menus and ribbons are easily customizable but not so in Excel 2007.

So far I can get macro shown as a button in the Quick Access Toolbar. But the issue is that I can't organize them or customize the icons.

Is there an accepted practice for this?

View 4 Replies View Related

Get The Menus

Dec 23, 2008

How to get the number of menus present in the excel?

View 9 Replies View Related

Drop Down Menus

May 22, 2008

I want to have a spread sheet where a user will pick an option from a drop down list, then based on that option choice, have a different cell populate with a specifc drop down menu.

Example:

Cell A1 has a drop down with three choices (1,2,3)
User selects option 1
Cell B1 will now have a drop down with options A,B,C
However:
If user selects option 2 from A1
Cell B1 will now have a drop down menu with options X,Y,Z

View 11 Replies View Related

Lost The Menus

Apr 3, 2009

Having recently started to get involved in Visual Basic I have managed to write some code that has removed all of my menus except for File and Help. In a blind panic I deleted what I think was the offending VB code in a vain attempt to return the menus. This didn't work. I am now stumped.

Also, I managed to change the way the Cell selection works when I press Return. When I press Return, Excel now selects the cell to the right of the one I was in rather than the one below it. Is there a way I can change this back?

View 5 Replies View Related

Creating Two Menus

Oct 11, 2009

I have a workbook which uses the code below to create a new menu upon opening. I have now used this same code in a second workbook to create a different menu. both work OK on their own However if by chance both workbooks are opened at the same time only one menu is shown. Is there a way I can change one of these to create a second menu if the first one is already there?

View 4 Replies View Related

Multiple Menus (add More)

Jan 13, 2010

I have this code which adds one menu at the top of the worksheet and I want to add more. My current sheet has a large number of scripts liatsed under it for different purposes and I'd like to divide it into different menus.

View 3 Replies View Related

Conditional Drop-down Menus ?

Oct 8, 2008

I would like to make a form where the user has to select from a drop down list several options, and that depending on the pick the drop down list below shows different options. example: For the question "what food do you want to eat?" you can select pasta, pizza or dessert.

Now if that person pics dessert, the 2nd drop down menu should shom him again a number of desserts he has to pick. And again below another one depending on what he chose.

View 2 Replies View Related

Menus, Inserting Line

Aug 6, 2009

I have a problem I am working on (see attachment for better understanding).

I have 4 sheets
1) quote
2) invoice
3) delivery note
4) products and settings

Now in the quote sheet I am trying to add a way of from a drop down menu, selecting the product from the products sheet and then a new line will be added automatically.

Then what ever is in the quote sheet is automatically copied to the invoice and delivery note sheet.

View 10 Replies View Related

IF / THEN For Validation Drop Down Menus?

Feb 8, 2010

If I have a cell that uses a list for drop downs (ie "Make" can be any of several different Fire Alarm Panel Types) and a second cell that I want to use to represent "Model Number" is there an IF the model is A from the Make list, THEN the next cells drop down list represents the model numbers under that Make?

View 2 Replies View Related

Drop Down Menus And Marco?

Jan 12, 2009

I am trying to program a drop down box at the top of a column so that when one of several options in the drop down are selected, a corresponding price list is displayed in the column below.

View 4 Replies View Related

User Forms / Menus

Nov 21, 2006

I am trying to graduate out of the crayon age of menus on a worksheet, to using a User Form Menu. Problem is, I don't understand how they work. I've created a test program using a simple form with an option button, a combo button, and a command button. The goal is if the option button is true, it places the color selected in the combo button on the worksheet. Here is my code, what am I missing to make it work?

Corrected code from my original request

Module 1

Public ClrCd As Integer

Sub OpenForm1()
UsrFrm1.Show
End Sub

View 9 Replies View Related

Drop Down Menus Movement

Aug 18, 2009

I am trying to force movement between multiple drop down menus. The menus are independent of one another. I want the user to only be able to select the information in the menus.

View 9 Replies View Related

Multiple Pull-down Menus

Mar 1, 2010

I have the following fields:

Manufacturer:System:Part Number:Material Description:
I want to make each field a "pulldown menu".

For simplicity, lets say I have three Manufacturers "A", "B" & "C"

Each Manufacturer has three systems "1", "2" & "3"

Each System has 100 parts "1-001" thru "1-100", "2-001" thru "2-100" & "3-001" thru "3-100". Each part has a corresponding description such as "Horizontal", "Vertical", "cover", etc...

I need to keep all of the manufacturers, systems, parts and descriptions in one excel workbook. I have no preference to how that information is built (tabular, list, etc..) I would like the workbook in which i want the fields above to be separate, so I only have to keep updating one master file that all of our takeoff sheets would be linked to.

when I select a manufacturer, I only want the available systems for that manufacturer to show up in the next pull down menu, then for that system I select I only want the available parts for that system to show up in the next menu. the descriptions need to correlate with the parts I select.

View 9 Replies View Related

Some Sub-Menus Disabled - Greyed Out

Nov 6, 2006

I have noticed that the submenus for the following main menu items are disabled

Edit Insert Format Data

The submenus for the other main menu items are fine.

I have not run any macros that alter the menus.

I have tried Tools>Customise Reset Menu and Toolbar usage. I then exit the Excel session, but the problem still remains,

View 3 Replies View Related

Validation Dropdown Menus Disappearing

Dec 10, 2004

I am compiling questionnaires in Excel and need to constrain the answers. I've successfully used validate, list and directed it to a list (containing, for example, 'USB', 'Firewire', 'SCSI'. When I first set this up, the drop down menu appeared (and the prompt message I had created). After I copied the first worksheet to use as the base for my next questionnaire, the drop down menu disappeared, although the prompt still shows and the validation still works.

Have I missed something fundamental?

I've tried creating one on a brand new workbook and copying the worksheet and it seems to function correctly. Why has my drop down menu vanished???

View 9 Replies View Related

Select Multiple Dropdown Menus At Once?

Mar 9, 2013

I have a 'diet diary' that I have built in excel that has many of the common meals I eat and details calories, carbohydrates, fats, etc and I use it to keep track of what I have eaten in the day.

However, one of the features is that on the front sheet I have lots of drop down menus that I select the different things (example, milk, banana, spaghetti bolognese) which in that puts in the nutritional info. However, each day I have different numbers of items to put in so I have to change the slots that no longer have an item in back to the default value so that it doesn't add calories. What would be ideal is if I could highlight a bunch of boxes at once and then make them all select the 'no-item' option in the drop down box simultaneously rather than selecting them all individually.

View 3 Replies View Related

How To Use Dropdown Menus Pointed To Value In Equation

Jul 18, 2013

What I would like to do is use a drop down list Type 1, Type 2, Type 3 etc and then be able to use whatever is selected to reference a value in another cell.

Excuse my ignorance but something like Cell 1 * (If Type 1 from the list is selected use figure in Cell 3, If Type 2 from the list is selected use figure in Cell 4..........) etc etc

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved