Home |
Search |
Today's Posts |
#1
|
|||
|
|||
No "Options" option
Hi guys,
I recently put the following code in a spreed sheet and lost the access to options (greyed out): Sub DisableCuts() Dim oCtls As CommandBarControls, oCtl As CommandBarControl Set oCtls = CommandBars.FindControls(ID:=21) ''Cut If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = False Next End If Set oCtls = CommandBars.FindControls(ID:=522) ''Options If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = False Next End If With Application .OnKey "^x", "" .OnKey "+{Del}", "" .CellDragAndDrop = False End With End Sub This has effected both home and work computers, the only ones that i installed this code in... Any ideas? Regards Keef |
#2
|
|||
|
|||
I posted that code. Along with the antidote that you must have lost:
Sub EnableCuts() Dim oCtls As CommandBarControls, oCtl As CommandBarControl Set oCtls = CommandBars.FindControls(ID:=21) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If Set oCtls = CommandBars.FindControls(ID:=522) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If With Application .OnKey "^x" .OnKey "+{Del}" .CellDragAndDrop = True End With End Sub -- Jim Rech Excel MVP "Keef" wrote in message ... | Hi guys, | I recently put the following code in a spreed sheet and lost the | access to options (greyed out): | | Sub DisableCuts() | Dim oCtls As CommandBarControls, oCtl As CommandBarControl | Set oCtls = CommandBars.FindControls(ID:=21) ''Cut | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | Set oCtls = CommandBars.FindControls(ID:=522) ''Options | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | With Application | .OnKey "^x", "" | .OnKey "+{Del}", "" | .CellDragAndDrop = False | End With | End Sub | | This has effected both home and work computers, the only ones that i | installed this code in... Any ideas? | | Regards | Keef | | |
#3
|
|||
|
|||
thanks jim,
i'm a dill i found it... i will give it a go regards keef "Jim Rech" wrote in message ... I posted that code. Along with the antidote that you must have lost: Sub EnableCuts() Dim oCtls As CommandBarControls, oCtl As CommandBarControl Set oCtls = CommandBars.FindControls(ID:=21) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If Set oCtls = CommandBars.FindControls(ID:=522) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If With Application .OnKey "^x" .OnKey "+{Del}" .CellDragAndDrop = True End With End Sub -- Jim Rech Excel MVP "Keef" wrote in message ... | Hi guys, | I recently put the following code in a spreed sheet and lost the | access to options (greyed out): | | Sub DisableCuts() | Dim oCtls As CommandBarControls, oCtl As CommandBarControl | Set oCtls = CommandBars.FindControls(ID:=21) ''Cut | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | Set oCtls = CommandBars.FindControls(ID:=522) ''Options | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | With Application | .OnKey "^x", "" | .OnKey "+{Del}", "" | .CellDragAndDrop = False | End With | End Sub | | This has effected both home and work computers, the only ones that i | installed this code in... Any ideas? | | Regards | Keef | | |
#4
|
|||
|
|||
it's funny how it effects excel for every spreedsheet i've done since???
"Jim Rech" wrote in message ... I posted that code. Along with the antidote that you must have lost: Sub EnableCuts() Dim oCtls As CommandBarControls, oCtl As CommandBarControl Set oCtls = CommandBars.FindControls(ID:=21) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If Set oCtls = CommandBars.FindControls(ID:=522) If Not oCtls Is Nothing Then For Each oCtl In oCtls oCtl.Enabled = True Next End If With Application .OnKey "^x" .OnKey "+{Del}" .CellDragAndDrop = True End With End Sub -- Jim Rech Excel MVP "Keef" wrote in message ... | Hi guys, | I recently put the following code in a spreed sheet and lost the | access to options (greyed out): | | Sub DisableCuts() | Dim oCtls As CommandBarControls, oCtl As CommandBarControl | Set oCtls = CommandBars.FindControls(ID:=21) ''Cut | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | Set oCtls = CommandBars.FindControls(ID:=522) ''Options | If Not oCtls Is Nothing Then | For Each oCtl In oCtls | oCtl.Enabled = False | Next | End If | With Application | .OnKey "^x", "" | .OnKey "+{Del}", "" | .CellDragAndDrop = False | End With | End Sub | | This has effected both home and work computers, the only ones that i | installed this code in... Any ideas? | | Regards | Keef | | |
#5
|
|||
|
|||
it's funny how it effects excel for every spreedsheet i've done since???
When changes are made to the menus or toolbars, either manually or by macros, they are recorded in you toolbar file (Excelx.XLB) and restored each time you restart Excel. The changes have no link to the workbook that happens to be open at the time. So when Options was disable, it's disabled "permanently". "Permanently" meaning until a macro enables it, or you delete your XLB file (with Excel closed) (this resets ALL menus/toolbars to their default settings), or you use View, Toolbars, Customize, Toolbars, pick a toolbar/menubar, and click Reset. -- Jim Rech Excel MVP |
#6
|
|||
|
|||
thanks for the tip jim, is there any way to disable cut in a workbook and
have it follow the workbook around, not harming the excel application? "Jim Rech" wrote in message ... it's funny how it effects excel for every spreedsheet i've done since??? When changes are made to the menus or toolbars, either manually or by macros, they are recorded in you toolbar file (Excelx.XLB) and restored each time you restart Excel. The changes have no link to the workbook that happens to be open at the time. So when Options was disable, it's disabled "permanently". "Permanently" meaning until a macro enables it, or you delete your XLB file (with Excel closed) (this resets ALL menus/toolbars to their default settings), or you use View, Toolbars, Customize, Toolbars, pick a toolbar/menubar, and click Reset. -- Jim Rech Excel MVP |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XML Data menu option -- not listed / available. How do you fix thi | Excel Discussion (Misc queries) | |||
Option Button on Forms Toolbar | Excel Discussion (Misc queries) | |||
How to protect Option button in worksheet | Excel Worksheet Functions | |||
Pivot Table Refresh Options | Excel Discussion (Misc queries) | |||
What does Insert>name>LABEL option do ? | Excel Discussion (Misc queries) |