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
my worksheet menu bar is displaying the ' Chart' menu no matter what I do. Not only do I have no charts in the workbook, (verified this by: )
For Each ws In Worksheets
ws.Select MsgBox ActiveSheet.ChartObjects.Count
Next ws
but adding worksheets, selecting various parts of a worksheet, creating a chart and deleting it, and everything else I've tried has no effect.
One interesting thing - the first chart I added (to test if it would 'unstick' itself upon deletion of the new chart) was named 'Chart 2', implying there was a chart1 that existed previously.... although I am fairly certain I never added a chart to this workbook at any point.
Also, the menu bar is working fine in other workbooks, and changes to chart and back to data like normal.
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.
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 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...............................
I run PCs at work and at home. Both machines run Windows XP and Office 2007
Home PC: Windows XP Home edition Version 5.1 (Service Pack 3) Excel 2007 SP1 MSO
Work PC:Windows XP Professional version 5.1 (Service Pack 3) Excel 2007 SP1 MSO
The VB was created on the home PC. It works. On the Work PC it crashes on the line with bold. Run-time error 9 - Subscript out of range. This have never happened before (or with any other VB routine). I also changed fileNew to include the full path, but same error. What has caused this? I suspect it’s the 'fileNew'.
I am trying to programmatically select an item from a single selection listbox (i.e., set to fmMultiSelectSingle). The following code works properly only every other time I run it; the problem is extremely repeatable.
Const storeStartCell = "B5000" Private Sub ListBox_CounterTOs_Click() selectedTOName = CStr(Mid(Worksheets("Sheet1"). Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 3, 3)) selectedTONumber = CInt(Mid(Worksheets("Sheet1").Range(storeStartCell).Offset(ListBox_CounterTOs.ListIndex, 0), 6, 4)) ListBox_TO_Name.Value = selectedTOName ListBox_TO_Number.Value = selectedTONumber End Sub
The times that it works, ListBox_TO_Name.Value is set to something like "ABC" and ListBox_TO_Number.Value is set to something like "1234". When the values get set properly, the associated click subroutines (e.g., ListBox_TO_Name_Click()) get called automatically when the value is set. The times that it doesn't work, they are both set to "" and the click subroutines do not get called. I also tried
I have the below code that changes the colour of a cell to green if the figure increases, and to red if it decreases.
The issue is that it works fine if you manually change the figure in A1 or A2 by entering the figure and hitting enter. If the cell figure changes in A1 by any other means such as RTD or say a value in C1 "=C1" then the colour change is not triggered.
Code: Public oldval, myval
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A2")) Is Nothing Then oldval = myval If Target.Value > oldval Then Range("A" & Target.Row).Interior.ColorIndex = 4
I am sending an email when the value of a cell changes, [url]
I am using the following code in my worksheet....
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range If Target.Cells.Count > 1 Then Exit Sub On Error GoTo EndMacro If Not Target.HasFormula Then Set rng = Target.Dependents If Not Intersect(Range("AE7"), rng) Is Nothing Then If Range("AE7").Value < 20 Then EmailOut 'MyMacroName End If End If
What I would like to do is increase the range that this applies to.
Range from AE7 to Range Y3:AE250.
I would like to Include the Range A3:A250 in the subject/body.
So If AE7 has changed, A7 (along with some text "This cell has changed, do X Y Z") would be somewhere within the email.
I have this macro that works perfectly. My boss wanted the subtotal lines within the report to be in a smaller font, so I added lines to the macro to do that. It won't work with those lines in there! It says the reference is not valid on the red line in the code below. The 2 blue sections are the ones I added to change the font size.
Code: 'Add subtotals at the end of each age bucket For i = 10 To LR Step 1 fr = Range("E" & i - 3).End(xlUp).Row If Range("B" & i).Value = "Totals" Then Range("E" & i).Formula = "=SUBTOTAL(9,E" & fr & ":E" & i - 2 & ")" With Range("E" & i & ":R" & i).FillRight
[code].....
Why would the font size make a difference? Is there some other way to change the font size that would work better?
