Add Controls To Right Click Menu Based On Range List
Sep 28, 2007
I have customised the right click menu's for this spreadsheet and currently it is all hard coded. I was wondering if it is possible to dynamically modify the right click menu? The code currently has right click buttons for each staff member, and when clicked other actions are performed. I have added a "Staff" sheet and was wondering if there is a way the code can reference that sheet and create the list based on those entries for example, when new staff join or other staff leave?
I'm sure that there is a better way to go about this, perhaps a For... Next loop but I don't know enough about it. In the mean time, I will keep bashing away at it in hopes of a brainwave... it's a Friday before a long weekend and I think my brain has decided its holiday time.
Option Explicit
Private Sub Workbook_Deactivate()
On Error Resume Next
With Application
. CommandBars("Cell").Controls("Add Nick").Delete
.CommandBars("Cell").Controls("Add Toby").Delete
.CommandBars("Cell").Controls("Add Ben").Delete
.CommandBars("Cell").Controls("Add Matt").Delete
.CommandBars("Cell").Controls("Add Zoe").Delete
.CommandBars("Cell").Controls("Add Anne").Delete
.CommandBars("Cell").Controls("Add Craig").Delete
.CommandBars("Cell").Controls("Add Unknown (1)").Delete
.CommandBars("Cell").Controls("Add Unknown (2)").Delete
.CommandBars("Cell").Controls("Remove").Delete
End With
With Application
.CommandBars("Cell").Controls("Cut").Visible = True
.CommandBars("Cell").Controls("Copy").Visible = True...............................
I am building an excel application that will allow the users to tick boxes which acknowledge completition of tasks. I then count up all the tickboxes to give them a score. However, I want to have multiple modes, so users can have different sets of tickboxes. Ideally, I would have a drop-down menu/ combo box at the top of the page with the 4 modes in, and then when selected the appropriate tick-boxes are shown. I considered creating 4 tickboxes in every location, and just showing them (object.hide/.show) when the option was selected in the combo box. I am currently using Excel Form Tickboxes, and counting themall, but if I did this method I would need to count them for each mode. would I need an array?
I want to be able to give my application menu when the user right-clicks on a specific series of cells. I've already got the code that will limit the right-click options to the cells that I want.
How do I then form and execute the menu that I want the user to see - "Archive Data", "Edit Data", "Add Data", for instance, rather than the normal right-click menu?
I have been modifying the options available on my right click menu, getting rid of about half of what was there and adding items I use quite often like Paste Values, Paste Formats, Paste Formulas, Sort..., AutoSum, and Format as Percent, as well as adding group dividers. I have it just the way I want it, but I have one issue. If the clipboard is empty, all of the paste buttons are disabled on the Standard toolbar and in the Edit menu as are the Paste and Paste Special... items on the right click menu, but the new paste items I added to my right click menu are still enabled. If I click on one, I get a "PasteSpecial method of Range class failed" error because I have no range selected. I got around this by adding On Error Resume Next to the macros of my added paste functions - not elegant, but functional. Is there a way to have those items appear grayed out and disabled on my right click menu when the clipboard is empty as opposed to my current workaround?
Question regarding the disabling of right mouse button click options and have used the code below to disable and reciprocal code to enable options. The code works well in Excel 2003 but have recently used the spreadsheet in excel 2000 and the code causes a runtime error 5 'Invalid procedure call or argument'. how to amend the code to have it also work with Excel 2000.
Ok, so I have a sheet with a working drop down list with dates, and depending on the date you choose, certain columns getting hidden and some un hidden. The layout is the same form is repeated 7 times, and which ever date you pick, the corresponding form gets un hidden. My question is, I have a button that will export it to a new sheet with just the values, to be sent to the office. I don't need to send the entire work book and confuse people.
I know how to export, just not using an if then to choose which columns to export.
I have a column with various drop down menu boxes and I need to add another selection to the menu list. My problem is, it's been so long since I did the drop downs, I have forgotten how to do it..
I created a right-click menu for userform textboxes from a code I found through googling. It works perfect, however, I don't know how to get it to work for more than one textbox.
Is it possible to create a menu that could be called by a double click which would allow you to click on a value from that menu and insert it into the double clicked cell. I know I could use a data validation list, but that is tacky and you have to scroll down the list to find the data you would like. I'd like to have a menu pop up that stores different values in 6 columns and 25 rows. Any way without having to jump into an access database?
I've run into this really strange problem with my right click menu (popup command bar) The UF that handles the cb is loaded at workook open event and it's modal* When first right click, the commandbar is invisible, but it's there. Only shadow seems, and when click on arrow, starts showing menu. I could say that something like repaint needed. If i click on anything, next time it popups normally !!
The problem does not occur if: 1) i load the uf manually 2)* display the UF modeless
Right click menus are a well known topic but I am struggling to find information on what I want to do with them. I have seen it done so I know its possible. What I want to do is completely replace the right click menu with my own custom one on a worksheet.
I do NOT want to add things to the existing right click menu I want to effectivly disable the existing one and put on my very own one in its place. I have looked at the code for adding a menu to a textbox etc which seems sound but I can't trigger it with an event. I don't have any code to show Im afraid as I am just playing with other people at the moment trying to get it to work.
I used the commandBar to create a right-click menu on a userForm but unfortunately I get an error 400 every time I click on the option "Edit" and I don't understand what I did wrong.
Sub CreateCmdBar() Dim st As CommandBar 'delete the pop-up if it exists On Error Resume Next Application.CommandBars("flexgrid_rc").Delete 'Disables enabled error handler in the current procedure and resets it to Nothing. On Error GoTo 0 On Error Goto 0 Set st = CommandBars.Add( Name:="flexgrid_rc", Position:=msoBarPopup, Temporary:=False) 'add two menu items to the new commandbar With st..................
I want to add Paste Values to the right click menu in Excel 2007. Can this be done without recording a separate macro to run it? What I mean is If I add the command itself to the right click menu, can it run of of the standard RightClick -> Paste Special -> Values option, or must I record a macro that does those steps each time?
I'm using Excel 2003 and am unable to find the name of, and therefore customise, the right-click shortcut menu that pops up when you right-click on a line drawn from the 'Drawing" toolbar. Basically, I'm trying to do some technical analysis on a chart. I want to be able to right-click on the lines that I draw on the chart and replicate a parallel line.
I have written some code that customises my Right-Click Menu. This works perfectly well but doesn't create the FaceIDs.Can anyone explain why? The Code is as follows
Sub CustomiseRightClickMenu() Dim cbnRightClickMenu As CommandBarButton Dim rngMacroNames As Range Dim intLoopCounter As Integer 'DEFINE THE RANGE CONTAINING ALL THE MACRO NAMES Set rngMacroNames = Sheet1.Range("MacroNames") 'DEFINE ARRAY OF FaceIDs arrFaceIDs = Array(255, 590, 350, 536, 576, 410, 401) On Error Resume Next With Application For Each Cell In rngMacroNames . CommandBars("Cell").Controls(Cell.Formula).Delete ..............
I'm working on a sheet which uses a fair amount of indirects, deleting or adding rows bugger these up, so I wanted to replace them with a macro which does if for them and keeps the structure intact. I've sorted the macro to do this and have sorted the macro to replace delete with my delete in the cell RC menu but the row RC menu only seems to work on around 50% of the PC's? All PC's are XP with XL 2003. The code I've used is detailed below. Is this a known issue with XL or something funky with some of the PC's?
With Application . CommandBars("Row").Controls("Delete Row").Delete Application.CommandBars("Row").FindControl(ID:=293).Delete Set cBut2 = .CommandBars("Row").Controls.Add(Temporary:=True, Before:=6) End With
With cBut2 .Caption = "Delete Row" .Style = msoButtonCaption .OnAction = "Delete_Row" End With
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.
I am developing a program for my electrical calculations (electrical panel). In the program, on my worksheet, I need all row inserts/deletes being watched and logged into another worksheet. I have become to a conclusion it would be best done by "taking over" default behaviour of built-in right-click menu commands like "Copy", "Paste" and "Delete".
In the ozgrid tips page I found some solutions how to deactivate those commands, but that is not what is best for me - when the menu item (or a corresponding key) is pressed, I would like to check if the ActiveCell (or ActiveRow) meats some criteria, and acording to that, make some changes in my "log" - and after - let further actions to default command behaviour. I think of it like "BeforeDelete", "BeforeInsert" or something. In fact the "log" is the worksheet with coordinates of my tables from the first worksheet, which should be changed according to new size of a table after the row insert or delete.
In Excel 2010 onwards (probably 2007 as well) there are two right click context menus that pop up on a cells.
The "Cell" command bar and also a smaller formatting bar.
What is this bar called and if you remove it how do you get it back when you have removed all the standard bars.
VB: Application.Commandbars("Cell").Reset
The above doesn't seem to get back the formatting bar. I did managed to get it back but through luck rather than judgement. Just through looping through every commadbar and printing it to the immediate window.
I've added the name of the context menu to each commadbar but the formatting one does not get a name put at the bottom of it. I've looked on this site and it doesn't seem to say anything about this new menu.
I searched a few times for the answer to my problem, using different search terms, but I've come up somewhat empty (this *might* be because I'm not up-to- speed with Excel's terminology?). Anyway, my problem is this: I have a workbook which is designed to track various data for a corporate training course ( sheets for attendance, grades, tool logins, etc). After all of these are sheets numbered 1-25 (we never have more than 25 students in a class, but usually around 20). The numbered sheets correspond to the student's number in the on the class list sheet (so, the first student would have personal "report card" information on sheet 1; the eleventh student in the class list would have their information on sheet 11, etc).
What I would like to do is have some way of putting a button on the class list sheet which would look at student list and rename the sheets to correspond to the student names. For example, if the fifth student listed was 'John Doe', it would rename his personal sheet ('5') to 'John Doe'. Obviously, I would need to do it for all of the students, and IDEALLY it would then get rid of any numbered sheets which are not used (having 21 students would only rename the first 21 sheets, but then hide sheets 22, 23, 24, and 25).
I am attempting to use a named range as my Validation data source but have two questions:
1) It seems when you use a named range as a source the user can enter in any data they want in that cell. I really only want the user restricted to the list I give them. If I enter the list manually in the source box then it works but I really want to use a named range
2) My range is fixed to 10 cells and the user can enter in as many variables as they want (up to ten). When a user clicks on one of the cells that the validation is set to read the range the user has to always scroll to get to the top. When selecting the cell Ecel always defaults to the bottom choice in the list. I want to minimize the VBA because Mac users will be using this and VBA seems to be touchy with macs.
how to display the result i made in macro to active worksheet so that i can keep a record of the result made in my random generator,
Sub timer() TextBox1 = "" TextBox2 = "" TextBox3 = "" TextBox4 = "" Dim x As String, i As Byte Randomize x = Format$(Int(Rnd * 1899) + 1, "0000") For i = 1 To Len(x) Me.Controls("TextBox" & i).Value = Mid$(x, i, 1) Application.Wait Now + TimeValue("00:00:03") Next
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
