No "Options" option

[Keef]

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

 

[Jim Rech]

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
| 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
|
|

 

[Keef]

thanks jim,
i'm a dill i found it... i will give it a go

regards
keef

 

[Keef]

it's funny how it effects excel for every spreedsheet i've done since???

 

[Jim Rech]

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.

 

[Keef]

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?