Disabling ALL commands on CELL Shortcut menu

Good afternoon, all!

Following on from earlier postings, I was previously able to delete all the
commands from the CELL shortcut menu and add two new menu items using the
following code:

Dim SCCellMenu As CommandBar
On Error Resume Next
Application.CommandBars("Cell").Reset
On Error GoTo 0
Set SCCellMenu = CommandBars("Cell")

With SCCellMenu
.Controls("Cut").Delete
.Controls("Copy").Delete
.Controls("Paste").Delete
.Controls("Paste Special...").Delete
.Controls("Insert Copied Cells...").Delete
.Controls("Insert...").Delete
.Controls("Delete...").Delete
.Controls("Clear Contents").Delete
.Controls("Insert Comment").Delete
.Controls("Format Cells...").Delete
.Controls("Pick From List...").Delete
.Controls("Hyperlink...").Delete
End With

With SCCellMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Insert Row"
.FaceId = 3194
.OnAction = "RowInsert"
End With
With SCCellMenu.Controls.Add(Type:=msoControlButton)
.Caption = "&Delete Row"
.FaceId = 293
.OnAction = "RowDelete"
End With
End Sub

The problem now arises that sometimes the Cell menu contains "Insert..." and
sometimes it contains "Insert Copied Cells...", which I assume are mutually
exclusive, so I can't have both in my code.

My question is, how do I test to see if a particular command is displayed
before I delete it, failing that, can I simply delete all commands using
something like the following:


'-------------------------------------------------------------------------------------------
'Disable commands on CELL shortcut menu like this, as sometimes "Insert"
and
'sometimes "Insert Copied Cells" is displayed
Dim SCCellMenuControl As CommandBarControl
X For Each SCCellMenuControl In SCCellMenu
SCCellMenuControl.Delete
Next

'-------------------------------------------------------------------------------------------
which incidentally doesn't work as it says "Object doesn't support this
property or method for the line beginning with the X.

I await your thoughts, VBA Jedi!

Regards and thanks in advance

Pete

Dim octl As Object
On Error Resume Next
Set octl = SCCellMenu.Controls("Insert")
If Not octl Is Nothing Then
octl.Delete
End If
Set octl = Nothing
Set octl = SCCellMenu.Controls("Insert Row")
If Not octl Is Nothing Then
octl.Delete
End If
On Error GoTo 0

--
HTH

Bob Phillips

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Good afternoon, all!
>
> Following on from earlier postings, I was previously able to delete all
the
> commands from the CELL shortcut menu and add two new menu items using the
> following code:
>
> Dim SCCellMenu As CommandBar
> On Error Resume Next
> Application.CommandBars("Cell").Reset
> On Error GoTo 0
> Set SCCellMenu = CommandBars("Cell")
>
> With SCCellMenu
> .Controls("Cut").Delete
> .Controls("Copy").Delete
> .Controls("Paste").Delete
> .Controls("Paste Special...").Delete
> .Controls("Insert Copied Cells...").Delete
> .Controls("Insert...").Delete
> .Controls("Delete...").Delete
> .Controls("Clear Contents").Delete
> .Controls("Insert Comment").Delete
> .Controls("Format Cells...").Delete
> .Controls("Pick From List...").Delete
> .Controls("Hyperlink...").Delete
> End With
>
> With SCCellMenu.Controls.Add(Type:=msoControlButton)
> .Caption = "&Insert Row"
> .FaceId = 3194
> .OnAction = "RowInsert"
> End With
> With SCCellMenu.Controls.Add(Type:=msoControlButton)
> .Caption = "&Delete Row"
> .FaceId = 293
> .OnAction = "RowDelete"
> End With
> End Sub
>
> The problem now arises that sometimes the Cell menu contains "Insert..."
and
> sometimes it contains "Insert Copied Cells...", which I assume are
mutually
> exclusive, so I can't have both in my code.
>
> My question is, how do I test to see if a particular command is displayed
> before I delete it, failing that, can I simply delete all commands using
> something like the following:
>
>
>
'---------------------------------------------------------------------------
----------------
> 'Disable commands on CELL shortcut menu like this, as sometimes
"Insert"
> and
> 'sometimes "Insert Copied Cells" is displayed
> Dim SCCellMenuControl As CommandBarControl
> X For Each SCCellMenuControl In SCCellMenu
> SCCellMenuControl.Delete
> Next
>
>
'---------------------------------------------------------------------------
----------------
> which incidentally doesn't work as it says "Object doesn't support this
> property or method for the line beginning with the X.
>
> I await your thoughts, VBA Jedi!
>
> Regards and thanks in advance
>
> Pete

Bob,

If Not SolutionDoesntWork then
SayThankYouToBob
End If

Thank you, Bob!

Regards

Pete

"Bob Phillips" wrote:

> Dim octl As Object
> On Error Resume Next
> Set octl = SCCellMenu.Controls("Insert")
> If Not octl Is Nothing Then
> octl.Delete
> End If
> Set octl = Nothing
> Set octl = SCCellMenu.Controls("Insert Row")
> If Not octl Is Nothing Then
> octl.Delete
> End If
> On Error GoTo 0
>
> --
> HTH
>
> Bob Phillips
>
> "Peter Rooney" <[email protected]> wrote in message
> news:[email protected]...
> > Good afternoon, all!
> >
> > Following on from earlier postings, I was previously able to delete all
> the
> > commands from the CELL shortcut menu and add two new menu items using the
> > following code:
> >
> > Dim SCCellMenu As CommandBar
> > On Error Resume Next
> > Application.CommandBars("Cell").Reset
> > On Error GoTo 0
> > Set SCCellMenu = CommandBars("Cell")
> >
> > With SCCellMenu
> > .Controls("Cut").Delete
> > .Controls("Copy").Delete
> > .Controls("Paste").Delete
> > .Controls("Paste Special...").Delete
> > .Controls("Insert Copied Cells...").Delete
> > .Controls("Insert...").Delete
> > .Controls("Delete...").Delete
> > .Controls("Clear Contents").Delete
> > .Controls("Insert Comment").Delete
> > .Controls("Format Cells...").Delete
> > .Controls("Pick From List...").Delete
> > .Controls("Hyperlink...").Delete
> > End With
> >
> > With SCCellMenu.Controls.Add(Type:=msoControlButton)
> > .Caption = "&Insert Row"
> > .FaceId = 3194
> > .OnAction = "RowInsert"
> > End With
> > With SCCellMenu.Controls.Add(Type:=msoControlButton)
> > .Caption = "&Delete Row"
> > .FaceId = 293
> > .OnAction = "RowDelete"
> > End With
> > End Sub
> >
> > The problem now arises that sometimes the Cell menu contains "Insert..."
> and
> > sometimes it contains "Insert Copied Cells...", which I assume are
> mutually
> > exclusive, so I can't have both in my code.
> >
> > My question is, how do I test to see if a particular command is displayed
> > before I delete it, failing that, can I simply delete all commands using
> > something like the following:
> >
> >
> >
> '---------------------------------------------------------------------------
> ----------------
> > 'Disable commands on CELL shortcut menu like this, as sometimes
> "Insert"
> > and
> > 'sometimes "Insert Copied Cells" is displayed
> > Dim SCCellMenuControl As CommandBarControl
> > X For Each SCCellMenuControl In SCCellMenu
> > SCCellMenuControl.Delete
> > Next
> >
> >
> '---------------------------------------------------------------------------
> ----------------
> > which incidentally doesn't work as it says "Object doesn't support this
> > property or method for the line beginning with the X.
> >
> > I await your thoughts, VBA Jedi!
> >
> > Regards and thanks in advance
> >
> > Pete
>
>
>

or just
On Error Resume Next
SCCellMenu.Controls("Insert").Delete
SCCellMenu.Controls("Insert Row").Delete
On Error goto 0

If you were really asking how to delete them.

--
Regards,
Tom Ogilvy

"Peter Rooney" <[email protected]> wrote in message
news:[email protected]...
> Bob,
>
> If Not SolutionDoesntWork then
> SayThankYouToBob
> End If
>
> Thank you, Bob!
>
> Regards
>
> Pete
>
> "Bob Phillips" wrote:
>
> > Dim octl As Object
> > On Error Resume Next
> > Set octl = SCCellMenu.Controls("Insert")
> > If Not octl Is Nothing Then
> > octl.Delete
> > End If
> > Set octl = Nothing
> > Set octl = SCCellMenu.Controls("Insert Row")
> > If Not octl Is Nothing Then
> > octl.Delete
> > End If
> > On Error GoTo 0
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Peter Rooney" <[email protected]> wrote in message
> > news:[email protected]...
> > > Good afternoon, all!
> > >
> > > Following on from earlier postings, I was previously able to delete
all
> > the
> > > commands from the CELL shortcut menu and add two new menu items using
the
> > > following code:
> > >
> > > Dim SCCellMenu As CommandBar
> > > On Error Resume Next
> > > Application.CommandBars("Cell").Reset
> > > On Error GoTo 0
> > > Set SCCellMenu = CommandBars("Cell")
> > >
> > > With SCCellMenu
> > > .Controls("Cut").Delete
> > > .Controls("Copy").Delete
> > > .Controls("Paste").Delete
> > > .Controls("Paste Special...").Delete
> > > .Controls("Insert Copied Cells...").Delete
> > > .Controls("Insert...").Delete
> > > .Controls("Delete...").Delete
> > > .Controls("Clear Contents").Delete
> > > .Controls("Insert Comment").Delete
> > > .Controls("Format Cells...").Delete
> > > .Controls("Pick From List...").Delete
> > > .Controls("Hyperlink...").Delete
> > > End With
> > >
> > > With SCCellMenu.Controls.Add(Type:=msoControlButton)
> > > .Caption = "&Insert Row"
> > > .FaceId = 3194
> > > .OnAction = "RowInsert"
> > > End With
> > > With SCCellMenu.Controls.Add(Type:=msoControlButton)
> > > .Caption = "&Delete Row"
> > > .FaceId = 293
> > > .OnAction = "RowDelete"
> > > End With
> > > End Sub
> > >
> > > The problem now arises that sometimes the Cell menu contains
"Insert..."
> > and
> > > sometimes it contains "Insert Copied Cells...", which I assume are
> > mutually
> > > exclusive, so I can't have both in my code.
> > >
> > > My question is, how do I test to see if a particular command is
displayed
> > > before I delete it, failing that, can I simply delete all commands
using
> > > something like the following:
> > >
> > >
> > >
> >
'---------------------------------------------------------------------------
> > ----------------
> > > 'Disable commands on CELL shortcut menu like this, as sometimes
> > "Insert"
> > > and
> > > 'sometimes "Insert Copied Cells" is displayed
> > > Dim SCCellMenuControl As CommandBarControl
> > > X For Each SCCellMenuControl In SCCellMenu
> > > SCCellMenuControl.Delete
> > > Next
> > >
> > >
> >
'---------------------------------------------------------------------------
> > ----------------
> > > which incidentally doesn't work as it says "Object doesn't support
this
> > > property or method for the line beginning with the X.
> > >
> > > I await your thoughts, VBA Jedi!
> > >
> > > Regards and thanks in advance
> > >
> > > Pete
> >
> >
> >