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.
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
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?
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?
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").
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.
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: ...
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.
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.
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.
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.
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.
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
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?
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?
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.
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.
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.
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?
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.
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?
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.
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.
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???
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.
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
I need to link this summary sheet projects to this plant and equipment list sheet to show each project numbers separate weekly cost hire on the summary sheet.
I am trying to add two drop down menus - part number and material description. I would like to be able to choose an item from either menu and have it populate the rest of the row with the item information.
I know how to create the drop down, but I don't know how to make it work so I can choose from either one of the two drop downs and have the other automatically populate.
