No "Options" option  
Author Message
JaapSchram





PostPosted: Mon Jan 31 14:38:23 CST 2005 Top

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

Excel35  
 
 
Jim





PostPosted: Mon Jan 31 14:38:23 CST 2005 Top

Excel Misc >> No "Options" option 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





PostPosted: Tue Feb 01 04:05:33 CST 2005 Top

Excel Misc >> No "Options" option thanks jim,
i'm a dill i found it... i will give it a go

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


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





PostPosted: Tue Feb 01 04:09:17 CST 2005 Top

Excel Misc >> No "Options" option it's funny how it effects excel for every spreedsheet i've done since???





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


 
 
Jim





PostPosted: Tue Feb 01 07:22:07 CST 2005 Top

Excel Misc >> No "Options" option >>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


 
 
Keef





PostPosted: Thu Feb 03 02:13:15 CST 2005 Top

Excel Misc >> No "Options" option 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?





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