Disable menus and toolbar

All examples in this article use false to disable the command bar control. If you want to enable this control, change it to true. Disable Code The "worksheet menu bar": application. commandbars ("worksheet menu bar "). the code below enabled = false disables the "standard" toolbar: application. commandbars ("standard "). the code below enabled = false disables the cell shortcut menu (that is, the menu displayed after right-clicking in the cell): application. commandbars ("cell "). enabled = false note: the English name of the command bar must be used in the code. If its local name is used, the code will not run. There are many examples on the internet, listing the name of the Excel command bar. You can also use the following code to list the English and local names in the Excel command bar. Sub get_commandbars_names () dim cbar as commandbar dim newws as worksheet dim rnum as long rnum = 1 Set newws = worksheets. add on error resume next activesheet. name = "commandbarnames" on error goto 0 for each cbar in application. commandbars newws. cells (rnum, ""). value = cbar. name newws. cells (rnum, "B "). value = cbar. namelocal rnum = rnum + 1 next cbar newws. columns. the code below the autofitend sub command bar disables all Command bar: Sub disable_command_bars_1 () 'Disable all command bar dim cbar as commandbar for each cbar in application. commandbars cbar. enabled = false nextend Sub: Disable a menu or control. If you want to disable a specified menu or control, you must know and use their IDs. You can find these resources at the address below: Click Download to display all the workbooks with icons and ID numbers. Click Download to display the command bar and the loaded macro of ID (1) disable the "file" menu in the menu bar in two ways under a menu in the menu toolbar. 1) use the name of the command bar control application. commandbars ("worksheet menu bar "). controls ("file (f )"). enabled = false note: the control name in the Code should use a local name, for example, the Chinese Excel version uses "file (f )". 2) use the ID number of the command bar control (this method applies to Excel versions in all languages) application. commandbars ("worksheet menu bar "). findcontrol (ID: = 30002 ). enabled = false because the idcode 30002 of the control used is unique, you can directly use the findcontrol method: application. commandbars. findcontrol (ID: = 30002 ). enabled = false (2) the code below disables the "full screen display" menu item under the "View" menu: application. commandbars ("worksheet menu bar "). findcontrol _ (ID: = 178, recursive: = true ). in the Enabled = false code, use recursive: = true to search for all sub-menus in the worksheet menu bar. The following code only disables the "copy" button control in the "standard" toolbar: application. commandbars ("standard "). findcontrol _ (ID: = 19 ). the Code under enabled = false disables the "copy" control in all command bar, including the menu bar, toolbar, and shortcut menu. In Excel-, use the following code (using office. commandbarcontrol): Sub menucontrol_false () 'excel 2000-2003 dim CTRL as office. commandbarcontrol for each CTRL in application. commandbars. findcontrols (ID: = 19) ctrl. enabled = false next ctrlend sub if you want to use it in Excel 97, use the following code: Sub menucontrol_enabled_false () 'excel 97-2003 dim CTL as commandbarcontrol dim cbar as integer on error resume next for cbar = 1 to applic Ation. commandbars. count for each CTL in application. commandbars (cbar ). controls application. commandbars (cbar ). findcontrol (ID: = 19, _ recursive: = true ). enabled = false next CTL next cbar on error goto 0end sub other 1) Hide the formula editing bar and the status bar application. displayformulabar = falseapplication. displaystatusbar = false2) the application. onkey. For example, to disable the "copy" shortcut key (CTRL + C), use the following code: application. onkey "^ C", "" to restore the shortcut key, use: application. onkey "^ C" 4) disable the right-click Application on the worksheet menu bar and other toolbar. commandbars ("toolbar list "). enabled = false5) prevents users from opening the "Custom" dialog box application. commandbars. disablecustomize = true prevents users from opening the "Custom" dialog box by right-clicking the toolbar or using the "Custom" menu item. 6) in the left-side navigation pane of the disabled worksheet, "type the question that requires help": application. commandbars. disableaskaquestiondropdown = true. After the code is run, the drop-down box is hidden. 7) resetting the command bar application. commandbars ("worksheet menu bar"). resetting a built-in control will restore the original behavior and attributes of the control. Resetting the built-in command bar removes the custom control and restores the built-in control. Sub AAA () dim AA, BB as Boolean AA = true BB = false application. commandbars ("Ply "). enabled = AA 'right-click the worksheet label to check whether the application is available. commandbars ("cell "). enabled = AA 'right-click the cell to check whether application is available. commandbars ("toolbar List "). enabled = A' right-click the toolbar and choose "View"> "toolbar" to check whether application is available. commandbars ("autocalculate "). enabled = A' right-click the status bar to check whether application is available. commandbars ("worksheet menu bar "). enabled = AA application. commandbars (1 ). enabled = AA 'the above two sentences are used to cancel the menu application. commandbars (2 ). enabled = AA 'unknown application temporarily. commandbars (3 ). enabled = AA 'common toolbar availability application. commandbars (3 ). controls (3 ). enabled = AA make the third "save" in the commonly used toolbar available for application. commandbars (4 ). enabled = AA 'format toolbar available application. commandbars (5 ). enabled = AA 'unknown application temporarily. commandbars (1 ). enabled = AA 'Restore menu available application. commandbars (1 ). controls (1 ). enabled = AA make the "file" in the menu available for application. commandbars ("file "). controls ("page settings (& U )... "). enabled = AA' whether "page settings" in "file" is available for application. commandbars (1 ). controls (4 ). enabled = AA: Indicates whether application is available for the fourth "insert" in the menu. commandbars (1 ). controls (4 ). caption = "Victor" 'change the name application. commandbars (1 ). reset 'Restore menu application. whether the application is displayed in the "displayformulabar = AA" Edit bar. displaystatusbar = AA' whether the status bar displays activewindow. displayheadings = AA' whether the row number column label displays activewindow. displayworkbooktabs = AA' whether the worksheet label displays activewindow. displayhorizontalscrollbar = A' whether the horizontal scroll bar displays activewindow. displayverticalscrollbar = A' whether the vertical scroll bar displays application. in caption = "wage statistics system", change "Microsoft Excel" to "wage statistics system" End sub