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.
I have a large workbook, with multiple sheets. I would like to be able to select a name from a drop down menu, and have the cell color change for all entries of that name in my other sheets. I can do it manually, but it would be much cleaner and easier to have Excel do it for me. I have looked at other answers here on this forum and know that if it can be done,
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.
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 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...............................
When one creates multiple UserForms with multiple (identical) TextBoxes, every control must have its own event handler procedures. All these TextBoxes in my workbook are to capture numeric data to populate various cells in the workbook.
Would you recommend using a Class Module to handle these events for TextBox controls, rather than having to repeat the event handler code for each control?
And if so, do you have some code that I can use that will cover most of the events and potential error handling routines for numeric input data?
I have a number of textboxes, or other boxes, using exactly the same code. In visual basic you can assign an index to these boxes and create one code where the index number specifies the box you are working with.
I have tried finding a way to do thing in VBA, but came up against a blank. I realise that this is either not possible or very simple, but right now I am stuck with the 'not possible'. Does anyone know if the 'very simple' is an option. It would greatly decrease the size of my program, make it easier to visualise and not make me change to much each time. Of course I refer to subs as much as possible making these routines 3 line routines (sub-call-endsub), but still there are a lot of textbox1_click() routines whereas textbox_click(index) would be nicer.
I have 2 textboxes, wherein I want them to be validated for Only numeric entries, and also that they should not be empty.
I can write 2 procedures for that, but then thats efficient coding... In the attached worksheet, step 1) select M+R in column 2 Step 2) make some entries in the 2 textboxes.
I have written some code, but thats not working...
My userform requires a user to enter amounts in 5 different textboxes.(textbox1-5) I have created a textbox6 to attempt to capture the totals (should be numerical) of textboxes1-5, even if this textbox figure is a 0 or a minus figure. I have browsed a few other posts with roughly the same issue and have come up with some basic code as per below... the code is pasted into each (textbox 1-5) textbox_change() code.
If TextBox1.Value = "" Then Exit Sub If TextBox2.Value = "" Then Exit Sub If TextBox3.Value = "" Then Exit Sub If TextBox4.Value = "" Then Exit Sub If TextBox5.Value = "" Then Exit Sub TextBox6.Value = CDbl(TextBox1.Value) + CDbl(TextBox2.Value) + CDbl(TextBox3.Value) + CDbl(TextBox4.Value) + CDbl(TextBox5.Value)