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

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

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

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

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

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