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

Disabling ALL commands on CELL Shortcut menu
 

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

Disabling ALL commands on CELL Shortcut menu
 

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

Disabling ALL commands on CELL Shortcut menu
 

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