I am trying to disable a shortcut menu for a commandbar I created. I am able to disable all shortcut menus for all commandbars with this:
Application.ShortcutMenus(1).Enabled = False
But, I'm not trying to disable rightclicking for all commandbars, just one. Is that possible? As far as I can tell, the "shorcutmenus" property is a direct child of application.
I have this code below which creates a CommandBar but my problem is that I thought the line of Code .Width 100 would make the width of the actual CommandBar increase if I adjusted that number, but this doesnt seem to be the case. The title in only shows Nursing Sta instead of Nursing Staff Rota. How can I make the CommandBar wider than is?
View 4 Replies View RelatedI've created a CommandBar Menu using Macros. It's not a linear code, I've used Do-While to create the different objects and embedded objects. As you can guess all objects refer to the same Macro. And in this Macro is were I've got the problem. I can't come with a code to get the name of the object or the index reference, and thus I can't create the actions for the different objects in the menu.
View 3 Replies View RelatedI have a right click event for a cell where I create a custom commandbar with single or multiple control buttons based on the cell value. I want to link each control button to another cell in the same sheet.
View 2 Replies View RelatedHow do you use the Application.CommandBars in the CommandBar Object to find a button.
View 1 Replies View Relatedwhen open xl app my addin is installing, but when excution gets to delete the my addin an error appears tells :invalid procedure call or argument.
Sub workbook_open
On Error Resume Next 'Just in case
'*Delete existing Super Menu if it was left.
Application. CommandBars("Worksheet Menu Bar").Controls("Accountant Menu").Delete
On Error Goto 0
End Sub
I have programmed an Excel file that removes all the standard commandbars from Excel and then opens a custom commandbar, I built, with limited functionality ( Named "Limited"). When I open this file on another computer, since that custom commandbar does not exist in that copy of Excel no Limited commandbar shows. So I created a macro that builds the custom commandbar when a file is opened. What I want is when the file is opened on subsequent times that it checks to see if that commandbar already exists. If it does, it skips the macro which builds this custom commandbar for the first time. It then disables the standard Excel commandbars (code written already) and enables the "Limited" commandbar (code aleardy written).
View 2 Replies View RelatedHow can I save a new xlt or xls so that the toolbar settings I am viewing will always reload with excel. I have tried purging all items from my xlstart locations, but unwanted (and unchecked) toolbars keep reappearing when I restart excel.
View 4 Replies View RelatedI have create a Command Bar, my problem I'm trying to understand is this
1.Can you amend the font colour of the any Captions on the CommandBar?
2.How can I get the Caption to pick up from a range and use the range name as the Caption?
I have named a range in a worksheet that I want to pick up and use as the range. Below is my CommandBar code
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 ?
'The following code creates a new commandbar name Visual Basic Editor, which on event delete the VBE module1. How can I tie the built in visual basic editor commandbar to this.
Sub bar()
Set mybar = CommandBars("Tools").Controls("Macro")
Set mycontrol = mybar.Controls _
.Add(Type:=msoControlButton)
With mycontrol
.Caption = "Visual Basic Editor"
.FaceId = 1
.OnAction = "MySub"
End With
mycontrol.Visible = True
End Sub
Function mysub()
With ThisWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines 1, .CountOfLines
End With
End Function
I have this following codes on cell G6
=VLOOKUP(G5,'P1'!K133:L135,2)
i want it to show an empty cell instead of #N/A if it returns 0 value...
Is there a way to disable 'Save as PDF or XPS' with the macro? I have tried disabling 'Save as' with the below VBA code but it does not disable 'Save as PDF or XPS'.
VB:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI Then
MsgBox "Save As is disabled", vbInformation
Cancel = True
End If
End Sub
Also I have a macro which saves the excel report in a PDF format with 'Save as PDF or XPS' utility. I would like to enable 'Save as PDF or XPS' when this macro is started and again disable 'Save as PDF or XPS' when this macro stops.
I have a button on excel form. I need to disable it once user has clicked on it.
I couldnt find name of it on the form anywhere or how to refer it to in the macro.
Excel 2003 - VBA - How do you detect when a row has been deleted in VBA? Better yet, how do you detect when a row is about to be deleted? I also would like to know the row number. I do not see an event handler that will inform me of the "Delete" event.
View 2 Replies View RelatedIs it possible to disable macro until next day once clicked to run
View 2 Replies View RelatedI need to disable cut and drag only in a workbook. The idea is to avoid formula reference errors when unlocked cells are cut and pasted or dragged. I can find many macros online but none that do only these two things.
View 2 Replies View RelatedI am looking for a way that I can ensure that Zero Values are never shown, either when you open a new workbook or open a new window. Preferable, one which I can install for any user who needs this. (such as via Book.XLA I think) but AutoExec is fine as long as it is alwys loaded.
View 1 Replies View RelatedI've got an excel sheet from a guy at work who decided to lock the entire document down so I can't do anything with it. Virtually all the ribbon options are greyed out, there isn't anything locking it in the code, I've tried checking the save as "read-only" option and it's not there. How I can actually save this is editable? I've looked in windows explorer and it's not read-only in there either. I tried save-as and even tried making it an XLS instead of an XLSM and it still won't let me edit it
View 1 Replies View RelatedI found this great code on this forum to disable copy/paste.
I would like to also disable ClearContents..
I can gather from the code that I just need to add a line similar to
EnableControl 755, False ' pastespecial
but change the # for the clearcontents command. But How can I find that number?
Sub DisableCopyCutAndPaste()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", ""
Application.OnKey "^v", ""
Application.OnKey "+{DEL}", ""
Application.OnKey "+{INSERT}", ""
Application.CellDragAndDrop = False
Application.OnDoubleClick = "Dummy"
CommandBars("ToolBar List").Enabled = False
End Sub
Need code or way of disabling the Right Click feature in Excel? I think you can change it in the computer settings but I only want to disable it for a particular workbook so I am hoping there is a VBA Code that will tell the user "Right Click Not Allowed, Please use Cut and Paste or Ctrl +V and Ctrl +C"
I am sure this can be done - I found a similar thread but in a different context to what I need.
I have a user of a workbook who insists on putting irritating red cornered comments in cells, rather than using the handy (and rather large) comments field I put in, especially for her.
i know in VB6 i could disable the X in the corner so someone can't X out of my form, and i see that's not an option in VBA.
So how can i stop the form from terminating when the user clicks that? i want them to be forced to use one of my buttons to close out and finish.
i tried adding something simple like Exit Sub to the Userform_Terminate() sub, but nothing. i can make code run when they click that, but i can't stop it from closing. what to do?
also, the form is being called by macro that does alot of other stuff before and after the form, so just opening a new instance of the form won't help unless you can stop my other code from continueing until the form is actually done...
I just want to know if there is anyway to disable the help feature in Microsoft Excel?
I have to administer some exams.
I have a sheet with a number of checkboxes, and I need to conditionally enable/disable some of them.
For example, if the user checks Box1, then the question for Box2 becomes relevant and Box2 becomes available. If Box1 is unchecked, Box2 needs to be unchecked and disabled.
I want to disable the X button on the top right hand corner in excel, but still allow the user with the option of closing the workbook via File and then scrolling down to EXIT. (Not close). I have disabled most of the functions in the Menu bar Close being one of them.
So only Exit is now enabled. When I click on the X it give me the following message as shown below advising me to go to the File menu to Exit, this bit I am happy with. However this is were my problem begins, when I go to File and Exit I get the same message as below and can not exit the application.
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
MsgBox "Please select EXIT from the File Menu.", vbCritical, "Cannot Close"
End Sub
Does anyone know if there is anyway that we can command Excel to avoid accepting aNY KEYBOARD ENTRIES? what intend to do is restrict a certain region on a worksheet for Users not to be able to type anything but but there are commandbuttons on the header which use: Range("C65536").End(xlUp)(2, 1) = "Value for the last cell" code to get the input into the cell. The purpose is to make use of the cmdbuttons with mouse but the data notto be keyed in !!!
View 2 Replies View RelatedI'm running a module macro which cause a CMB change (and an additional macro run) in one of the sheets.
Since this interrup the main macro run (and also waste unnecessary time) I would like to disable it.
I would like to be able to disable a workbook from being able to be sorted, but at the same time allow fitering.
I have tried the following code, but have not got it to work correctly, does any one see what I need to add or remove to get this to work.
Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Application
. ScreenUpdating = False
.DisplayAlerts = False
ActiveSheet.Sort
MsgBox "You Can't sort sheets in this workbook!"
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
i have this code to disable X in a MsgBox.......
MsgBox "Closing from X is Disabled", 20, "Action"
Is there another way that the MsgBox shows OK only botton without showing yes/no botton