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
ADVERTISEMENT
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
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
View Related
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
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
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
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
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
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
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
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
Jun 25, 2014
I'm trying to find a way to save a single sheet of an excel workbook and in the same process delete all vba code and shapes from the new single sheet workbook. I was looking around and found this code which does save only the single sheet to a new one sheet workbook but doesn't delete the vba and shape that I have used to assign macros to in the original.
Code:
Sub SaveSheetAsNewBook()
Dim wb As Workbook
Dim InitFileName As String
Dim fileSaveName As String
Dim wshape As Shape
InitFileName = ThisWorkbook.Path & Format(Date, "mm.dd.yy")
[code]....
View 2 Replies
View Related
Sep 17, 2009
I have multiple workbooks ( with unique names) under same folder. Each workbook has "Report-Corn" sheet.
I would like to copy cell "P15" from each (workbook-> report-corn -> P15) and paste to Master.xls workbook -> Sheet 1 one after another which is also located under same folder.
View 9 Replies
View Related
Oct 3, 2006
I have default settings for new workbooks and worksheets including,
zoom=86/Arial 8/, 0.00/ etc.
I set these up using the .xlt file for templates and they work correctly.
However, when I create a new window under the "Window" menu item, it opens the new window with different (Excel Default?) settings.
Can this new window be made to open with my user-defined settings?
View 8 Replies
View Related
Apr 6, 2007
I`m building a macro for which I would like to save a parameter for the macro to read and set.
I would like this parameter to be saved (permanently) in something like a custom workbook property, i.e. Tarabo (=Yes/No).
How can I create/read/wright using vba code?
View 9 Replies
View Related
Feb 8, 2008
I have created a macro and would like to use it in other workbooks. Can this be saved to the tool bar for this purpose?
View 2 Replies
View Related
Apr 14, 2009
I have created a workbook and made a custom toolbar to perform the macro functions for the sheets in the workbook. Everything works great for me, but I want to attach the custom toolbar to the workbook so that whenever someone else pulls up the workbook they pull up the custom toolbar too.
I used the tools menu, clicked customize, toolbars, attach, selected the custom toolbar and clicked copy.
No toolbar appears.
I can select View, Toolbars, and select the custom toolbar, then the toolbar appears, but when I close the sheet the toolbar remains.
When someone else opens the sheet, the toolbar appears without any "buttons." It is an empty toolbar.
The help text isn't helping because the instructions for attaching a custom toolbar to a worksheet (tools/customize/toolbars/attach/copy) do not attach the toolbar to the workbook.
View 6 Replies
View Related
Jun 12, 2014
How can I have a custom defined workbook run a particular macro?
I'm using the Run Application Method:
Application.Run("'Workbook.xls'!Macro")
However the workbook.xls field is static as my program loops through a folder containing various workbooks, opening these workbooks, and running a specific macro housed in their specific module.
Dim MyFile as Object
MyFile = Dir("C:Test")
While MyFile ""
Workbooks.open Filename:="C:Test" & MyFile
Application.Run("'Workbook.xls'!Macro")
MyFile = Dir
How can I define the current active workbook in the run method? Using the custom 'MyFile' in place of the workbook does not work.
Is there a call module method available?
View 3 Replies
View Related
May 12, 2006
I'm trying to load a custom menu in a workbook to avoid having users to load an add in to use the spreadsheet. I'm trying to add this to the "this workbook" tab but I've never done this.
View 3 Replies
View Related
Nov 30, 2007
I have downloaded a custom menu workbook attached. I am wanting to only show the custom menu in one workbook as it appears in all workbooks open at the time.
View 3 Replies
View Related
Apr 17, 2014
how to merge different workbook in a single excel workbook. E.g. letus one workbook name is ram and another is shyam i want to merge both and make another workbook in excel like ram or shyam or another s..
View 1 Replies
View Related
May 25, 2012
I am building a macro and am just not figuring out how to do this. I am trying to create a new workbook, then give it a name. Simple right? Well, I have the format for the file name to be "mm-dd-yy Transmittal #.xlsx" but I can't figure out how to make the # part of that equation start as 1 but if the file already exists change it to 2, if that exists, change it to 3, and so on. Here's what I have so far:
Code:
Sub Transmittal()
Dim NewBook As Workbook
Dim MyPath As String
[Code]....
I don't think I'm allowed to do "x + 1" so that is probably where my problem is coming from.
View 9 Replies
View Related
Jan 6, 2013
I want to create a custom tool bar for a work book but when I right click in the unused part of the tool bar area the only options I get are customize Quick access tool bar and customize the ribbon.
View 3 Replies
View Related
Dec 30, 2008
I created 3 custom toolbars. I want toolbar A to be visible only in Workbook A.xls, toolbar B to be visible only in Workbook B.xls and toolbar C to be visible only in Workbook C.xls. while Workbooks A,B, and C are open at the same time. I use Workbook A to automatically open Workbooks B and C.
Problem: Since I'm openning the Workbooks automatically it only keeps the Toolbar C visible on all Woksheets.
I included the following code in the This Workbook Object on each Workbook:
For Workbook A:
Private Sub Workbook_Open()
Application.CommandBars("A").Visible = True
Application.CommandBars("B").Visible = False
Application.CommandBars("C").Visible = False
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("A").Visible = False
Application.CommandBars("B").Visible = False
Application.CommandBars("C").Visible = False..............................
View 9 Replies
View Related
Jun 2, 2006
I had made a Workbook with my own command Bars, and everthing is ok .. the problem is when i copy it to anthor pc. i have got an error msg. and when i check the code (debug) i've found the problem is with that command bars ( it's missing )
and now the Q is:
how to copy a workbook with it's all add-in's and customised bars ?
View 2 Replies
View Related
Jan 20, 2008
The following code for a custom menu is used in a workbook which has two sheets with
embedded charts, two chart sheets and several sheets for calculations
and information
In This Workbook
Private Sub Workbook_Activate() 'Changed Activate to Open
Run "AddMenus"
End Sub
and
Private Sub Workbook_Deactivate() 'Changed Deactivate to Close
Run "DeleteMenu"
End Sub
and
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Run "DeleteMenu"
End Sub
View 9 Replies
View Related
Jun 6, 2014
Is there any way to filter/sort a workbook by a specific text. (EX. Unit 17) I have a spread sheet with 40,000 plus rows and in 1 column it has descriptions. I am needing the filter to filter out all occurrences of Unit 17 and Unit 16. They will not always say the something happened to them. EX Repair brakes on Unit 17 or maybe repair tires on Unit 17...
View 3 Replies
View Related
Oct 5, 2012
I am working on building a custom menu and I am looking for the code that would open a specific workbook execute a macro when I select the tab, can this be done????
View 1 Replies
View Related
May 17, 2008
I cannot figure out how to get my error handler to work, or actually, not work. It seems to work fine when there is an error, but the code still gets read even when there was not an error. Basically, I am trying to open a file, which may or may not be there. When it is not there I want a message to pop up informing the user. However, when the file is there and it opens, the error handler still gives the message box. Any ideas what I am doing wrong?
Private Sub btnOK_Click()
Application. ScreenUpdating = False
Dim LCSfile As String
LCSfile = frmSelectFile.Listbox1.Value
On Error Goto ErrHandler
Workbooks.Open Filename:=sPath & sDate & "" & LCSfile & "QUANT.CSV"
ErrHandler:
MsgBox ("File is not quantitated. Please select another file.")
Application.ScreenUpdating = True
End Sub
View 2 Replies
View Related
Jul 27, 2009
I have two workbooks that I'd like to merge together into a single workbook. Or, pull the worksheet out of one and insert it into another.
View 2 Replies
View Related