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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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