Why Delete Menu Items Before Closing?

Oct 23, 2008

Came across an example in a VBA book, wrt adding custom menu items. This example included code for deleting the menu item before closing the workbook.

Is there a reason you'd want to do this? The example already had provisions to delete the menu item if it already existed before the code ran, so you wouldn;t end up with multiple items...

View 7 Replies


ADVERTISEMENT

Sample Code To Add/delete Custom Menu Items

Feb 22, 2007

Option Explicit
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCustomMenu As CommandBarControl

'(2)

Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")

'(4)

Set cbcCustomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup)

'(5) Give the control a caption................

I have created several spreadsheet with customized menus. In step 5 when I type
a period the options for selecting Caption does not appear. In step 6 the 'Control' word didn't appear in list. Sometimes the options in step 6 do appear BUT not the option for Controls. I have created spreadsheets with menus that work but the only way I could do it was to cut and paste from the website and then modify it.

View 3 Replies View Related

Counting Menu Items In Menu Bar

May 20, 2006

Suppose I delete a (unknown) number of items from the Worksheet Menu Bar. This can vary because of the fact that my excel sheet is being used by a different number of people, all with a different Worksheet Menu Bar configuration. Is there a way of counting how many Menu items are still left in the Worksheet Menu Bar?

So, suppose all that is left (after using the program) in the Worksheet Menu Bar are Excel, File, Tools, Help. How can the porgram find out that there are only 4 items in the menu?

View 3 Replies View Related

Menu Items In UserForm For GUI

Mar 3, 2008

i'm trying to build a GUI-only application using Excel Userforms, with File and Help menus on the form. i'm coming from Java where i know getting a menu on a GUI is possible, but i haven't seen any resources for it in VBA Excel Userforms. is it possible to place menu items in a userform?

View 4 Replies View Related

Moving Personal Items To TOP Of Menu

Dec 30, 2008

One can create additional menu items for the right click, and these will show up always at the bottom. Is it possible to force them to appear at the top of the menu list above cut? This is specific to Macro created items.

View 4 Replies View Related

Add Menu Containing Items For User Access?

Mar 30, 2008

What's the easiest/best way to add a menu containing menu items so that a user can easily access custom vba-functionality?

View 2 Replies View Related

Dynamically Adding Menu Items

Jan 22, 2010

I have created a menu called "Temp" and a sub menu called "Temp1". When the user click on Temp1, I need to dynamically add some menu items under Temp1. Is this possible in Excel?

I need to do this on the right click menu. for example, along with "Cut", paste" etc menu's I need to add a Menu Item, Temp and under this menu, I need to add a SubMenu, Temp1. When the user click on the Temp1, I need to populate the child items.

View 9 Replies View Related

Create Custom Menu Items For Add-in

Feb 15, 2010

I have created an Addin from one I found on this site which creates a menu on the CommandBar. On this menu I can add my Macros. The Menu is initially supposed to perform a delete function then an add, just in case the menu already exists on the CommandBar. It is also supposed to Uninstall the menu when you de-select the Addin. At the moment it doesn't seem to be doing either, as I have now got 5 CommandBar menu's all the same and I can't delete them?

This also creates a problem of when I add another macro, the menu on the CommandBar doesn't update with the addition?

I have attached the .xla file for you to look at and see where I'm going wrong.

VBA Macros & Creating An Add-in For Them

View 5 Replies View Related

Menu Items Disabled While In Formula Bar

Oct 3, 2007

How do I disable a custom menu item when a user is entering data in a cell?

For example... when the formula bar in Excel has focus, and a user tries to select Data > Sort, the Sort item is disabled. But when the focus is back to the worksheet, Data > Sort is enabled.

I need to do the same thing for my custom menu items.

View 5 Replies View Related

Disable Menu Items In 2007

Jan 24, 2008

How can I disable the SAVE and PRINT options under Office Button, in excel 2007??? I've tried a macro that I used in 2003 version, but it not works (problem: "File" menu)!!!

View 2 Replies View Related

Multiple Menu Items Under One Heading 2003

Jun 23, 2009

I've got several add-ins that I'd like the end user to install and have accessible under one menu item ("Assessments") in Excel 2003. So far, the code below successfully adds the item, but it only shows the item of one add-in. How can I get it to show all the items under the "Assessments" category?

Private Sub Workbook_Activate()
Run "AddMenus"
End Sub

Private Sub Workbook_Deactivate()
Run "DeleteMenu"
End Sub

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

View 9 Replies View Related

Disable Save & SaveAs Menu Items

Jul 13, 2006

Is there a code or a way to make it impossible to make permanent changes to a file without first saving it as something else? I have a master report that is done monthly.

I do not want anyone to be able to save any changes on the master report. How do I go about disabling save and the save as funtions in excel? I have a save as command button that I only want people to use.

Also is there a way to set up a print command button to print to any computers default printer? I have set up a print command button on my computer and it works fine on mine, but I have sent the file to another person and the button is not doing anything on their computer. I assume because we don't have the same computers.

View 6 Replies View Related

Items Greyed Out On Sheet Right Click Menu

Jul 11, 2007

I cannot figure out why this setting has changed. When I right click my tab worksheet in Excel to copy or rename, it does not allow me. I will need your advice to re-activate that options so it is visible and not gray. I tried in the VBA workbook properties settings with no luck and also Tools, Options.

View 6 Replies View Related

VBA - Syntax Delete Data From Clipboard Before Closing A File

Feb 22, 2008

I recorded a Macro to open a workbook, copy and paste data from one excel workbook to another, and close the workbook which was opened. Everything works fine except:

because of the amount of data that I am copying, I get a message box saying "There is a large amount of information on the Clipboard. Do you want to be able to paste this information into another prgoram later?" with a Yes/No button.

So now I have to click the "No" button before my macro closes the excel file. Is there a simple line of code that will by-pass this?

I am assuming it has to do with the following:

Windows("Book1.xls").Activate
ActiveWindow.Close , savechanges:=False

View 5 Replies View Related

Excel - Delete Unwanted Rows On Closing File

Dec 30, 2011

I want to delete some unwanted rows on closing my excel file. the blank rows are between set of datas. so it has to check all the blank rows, delete it until the last one. example

data
blank rows
data
blank rows
data
blank rows
data

View 4 Replies View Related

Delete Sub Menu

May 16, 2007

i use Excel2007

a menu appears under the Addin command, it appears from using an Addin, the addin was deleted, how can i delete this menu now ?

i trid this code,

Sub t()
Application. CommandBars("Add-Ins").Controls("Custom Toolbars").Delete
End Sub

View 3 Replies View Related

Disable The Delete Function From The Menu

Oct 13, 2009

I am creating a fool proof, well as near as I can get it, spreadsheet for them to work in and want them to follow the rules I set out!!

I am able to disable cut, copy, paste etc but was wondering if it was possible for to disable to use of the "Delete" function from the menu when you right click on a cell and also disable to the Delete key on the keyboard?

View 8 Replies View Related

Disabling Delete Sheet From Menu

Jul 19, 2007

How do I diable the "Delete Sheet" so user can not delete any sheets

View 9 Replies View Related

Delete Custom Menu Bar / Toolbar

Oct 26, 2006

I was making a custom Menu Bar in excel. In order to troubleshoot I executed the code a no. of times, resulting in multiple but same menu bars in excel application. Now the problem is that even if I quit excel, the menu bars dont seem to go..How do I get rid of them.

View 2 Replies View Related

Allow Items To Be Added To "cell" Shortcut Menu

May 19, 2007

I am using the following code to add an item to the right click " cell" shortcut menu.

I placed it in a Worksheet_Activate module in ThisWorkbook. I'm pretty sure the code is correct but it seems something is preventing the code from executing properly because no item is added to the menu. Is there some setting in a workbook that might prevent changes to the right click menu from being made? How can I get this code to execute properly?

'Set NewItem = CommandBars("Cell").Controls.Add
'With NewItem
' .Caption = "MACRO1"
' .OnAction = "MACRO1"
' .BeginGroup = True
'End With

View 7 Replies View Related

Closing Workbook When Closing Userform

Sep 14, 2006

I have a userform which opens when the workbook opens. Ideally Id like to have the userform open without the workbook coming up and have the workbook close when you close the userform. If thats not doable then is there a way to just close the workbook when the userform is closed?

View 3 Replies View Related

Add/Delete Items To Combobox

May 30, 2009

I want to know the procedure of adding and deleting the items to a combobox or list box dynamically.

View 13 Replies View Related

Macro To Delete Items In A Folder

Aug 14, 2013

I have macro to copy data from one folder to another. However I would like a macro to delete the files in the source folder once copied

View 6 Replies View Related

Compare Sheets And Delete Same Items

May 18, 2006

I have two sheets with about 7000 records in each. They both have two columns only. One is the master list. I want both of the files to be compared and then all the duplicate entries to be deleted from both files.

Ultimately I want to have only the entries in the master sheet that are not in the other sheet.

View 3 Replies View Related

Macro To Delete Rows And Renumber Items After

Apr 8, 2014

Looking for a userform command button macro "CommandButton1" to initiate a deletion of rows. Essentially "Combobox1" is a list of "Text" rows in columnA (after row16). I want the command button to delete the "Text" row chosen in the combobox and all rows below it until the next "Text" row. After this I want all "Number" rows below the chosen "Text" row to be renumbered by subtracting "1.000". The sequence of order I guess doesn't really matter as long as the desired outcome is achieved (meaning if for vba purposes it is better to renumber all rows below the "Text" row chosen then delete the "Text" row and rows between next "Text" row that is perfectly fine).

View 14 Replies View Related

Delete Items From Shortcut Menus [Right Click]

Dec 30, 2008

I read one of the threads that talked about adding commands to the right click menu. I added a few commands to try out, but now I don't know how to delete them.

View 4 Replies View Related

Code To Search For Items Copy To Different Sheet And Delete Them

Jan 27, 2014

I have a workbook with one sheet (called "Pipeline") and another sheet called "Completed". The Pipeline sheet is used to keep track of all of the tasks that the team are working on, and then when the task is completed, it needs to be moved to the "Completed tab".

I currently have managed to write some code that looks for items marked "completed" in row G of the "Pipeline" sheet, then copies them over to the "Completed" tab, and then deletes the row from the "Pipeline" sheet.

There is one macro to copy the completed cells (called "Transfer"), and then another to delete the cells (called "Delete"). I then just have macro called "Clean" that just calls the transfer macro, and then calls the delete macro.

Often, there are 6 tasks marked complete, and only some of them will be copied over, but all of them will be deleted (a disaster).

Transfer Macro : [Code]....

Delete Macro : [Code]....

Overall Macro : [Code]....

View 7 Replies View Related

List Box Depending On Combobox And Add Or Delete Items To Range

Jul 4, 2009

i had a user form with a Combo box,list box,text box and a command button. I need the code that works upon selecting:

1)An Item from the combo box should display the list in the list box.

eg: If Country is selected from the combobox then the list box should contain all the names of the countries from the country Column

2)An item should be added to the list in the worksheet when an item is entered in the textbox.

Like wise, when an item is selected from the combobox from "Delete Items List" all the items relating to the item selected from the combobox should be displayed in the list box and a choice to delete the items relating to the combobox item should be provided.

View 4 Replies View Related

Replace Standard Shortcut Menu With Custom Menu?

Jun 24, 2014

I have created a sub to add new controls to the "Cell" shortcut menu. Can I remove the standard "Cell" shortcut menu controls? if so how do you do it?

[Code] ......

View 4 Replies View Related

Menu Bar/Navigation :: Tool That Creates A Menu Bar Or Code?

Oct 20, 2009

I have a workbook with multiple sheets. Is there a tool that creates a menu bar or code that I can add that would support navigation?

My goal is to have something with approximately 4 options (buttons?). They would link to 4 different sheets that serve as indexes within the workbook.

I know I can add a control like a button to a given sheet that would take the user to given sheet when clicked, but can I make the button 'float' above all the sheets, or add to a menu bar, or something? I just don't want to have to add the button to every sheet in the workbook.

I am looking for something simple/easy, and am not looking for anybody to do the work. I just have no idea on how to approach the problem.

View 11 Replies View Related







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