Disable Menu Items Insert/Delete Entire Column(s)
 

I have a spreadsheet which has some macros associated with it and need to
make sure that columns are not deleted or inserted. For the case where the
context menu is used to delete or insert a columns, the following code works
because one has to select the entire column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 65536 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub
(The above code places the spreadsheet in the protection mode if an
entire column is selected and unprotects it when a cell or range of cells is
selected.)

However, this does not work if someone decides to delete or insert using the
Excel menu. I don't want to keep the sheet in a protected mode since this is
too restrictive, but I do need a way to protect the columns. Any help with
this is appreciated.

Disable Menu Items Insert/Delete Entire Column(s)
 

You could have a custom menu.
Or you could temporarily disable the "Edit" and "Insert" menus from the menu
bar.
As far as I can remember you could be more specific and disable only some of
the sub menus of Edit and Insert.

If this will suit you, let me know and I'll try and look up one of my old
programs as to how I did it.

Chas

"JR_06062005" wrote:

I have a spreadsheet which has some macros associated with it and need to
make sure that columns are not deleted or inserted. For the case where the
context menu is used to delete or insert a columns, the following code works
because one has to select the entire column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 65536 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub
(The above code places the spreadsheet in the protection mode if an
entire column is selected and unprotects it when a cell or range of cells is
selected.)

However, this does not work if someone decides to delete or insert using the
Excel menu. I don't want to keep the sheet in a protected mode since this is
too restrictive, but I do need a way to protect the columns. Any help with
this is appreciated.

Disable Menu Items Insert/Delete Entire Column(s)
 

That sounds like a good idea. Do you think you could help me a little with
the code to do this? I created a menu which duplicates the main menu except
under Edit the Delete option has been removed and under Insert the Columns
has been removed. The menu is called tmpMenuBar.

Dim oldMbar As CommandBar, newMbar As CommandBar
Set oldMbar = CommandBars.ActiveMenuBar
Set newMbar = CommandBars.Add _
(Name:="tmpMenuBar", Position:=msoBarRight, _
MenuBar:=True, temporary:=True)
With newMbar
.Visible = True
.Protection = msoBarNoMove
End With

The message I get when I try to run the code is "Invalid procedure or call."
Since this is a modified call from Microsoft VB Help, I am guessing that I
need to add a library, but have no idea which one.

"ChasAA" wrote:

You could have a custom menu.
Or you could temporarily disable the "Edit" and "Insert" menus from the menu
bar.
As far as I can remember you could be more specific and disable only some of
the sub menus of Edit and Insert.

If this will suit you, let me know and I'll try and look up one of my old
programs as to how I did it.

Chas

"JR_06062005" wrote:

I have a spreadsheet which has some macros associated with it and need to
make sure that columns are not deleted or inserted. For the case where the
context menu is used to delete or insert a columns, the following code works
because one has to select the entire column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 65536 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub
(The above code places the spreadsheet in the protection mode if an
entire column is selected and unprotects it when a cell or range of cells is
selected.)

However, this does not work if someone decides to delete or insert using the
Excel menu. I don't want to keep the sheet in a protected mode since this is
too restrictive, but I do need a way to protect the columns. Any help with
this is appreciated.

Disable Menu Items Insert/Delete Entire Column(s)
 

Hello JR,
Yes I will be more than happy to help.
I tried your code and it works as it should. (You are deleting all the menu
items off the screen).
Maybe later you are adding some of the them to the new menu bar.

The four references I have a (The ones listed under "Tools" "References"
in the VBE window).
I am using Excel 2003, Windows XP

Visual Basic for Applications
Microsoft Excel 11.0 Excel Object
OLE Automation
Microsoft Office 11.0 Object Library

The code part that I am using is in my program is:

Set myMenuBar = CommandBars.Add _
(name:="mBar", position:=msoBarTop, MenuBar:=True, Temporary:=True)

Which is not unlike yours.

Please post the whole code routine and where the error is occuring and I'll
see if I can see anything.

Incidentally you might want to disable the mouse right click also. You can
do this by placing the following in the WorkBook Code Module:

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target
As Range, Cancel As Boolean)
MsgBox ("You cannot use the Right Click Menu")
Cancel = True
End Sub

Good Luck

Chas

"JR_06062005" wrote:

That sounds like a good idea. Do you think you could help me a little with
the code to do this? I created a menu which duplicates the main menu except
under Edit the Delete option has been removed and under Insert the Columns
has been removed. The menu is called tmpMenuBar.

Dim oldMbar As CommandBar, newMbar As CommandBar
Set oldMbar = CommandBars.ActiveMenuBar
Set newMbar = CommandBars.Add _
(Name:="tmpMenuBar", Position:=msoBarRight, _
MenuBar:=True, temporary:=True)
With newMbar
.Visible = True
.Protection = msoBarNoMove
End With

The message I get when I try to run the code is "Invalid procedure or call."
Since this is a modified call from Microsoft VB Help, I am guessing that I
need to add a library, but have no idea which one.

"ChasAA" wrote:

You could have a custom menu.
Or you could temporarily disable the "Edit" and "Insert" menus from the menu
bar.
As far as I can remember you could be more specific and disable only some of
the sub menus of Edit and Insert.

If this will suit you, let me know and I'll try and look up one of my old
programs as to how I did it.

Chas

"JR_06062005" wrote:

I have a spreadsheet which has some macros associated with it and need to
make sure that columns are not deleted or inserted. For the case where the
context menu is used to delete or insert a columns, the following code works
because one has to select the entire column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 65536 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub
(The above code places the spreadsheet in the protection mode if an
entire column is selected and unprotects it when a cell or range of cells is
selected.)

However, this does not work if someone decides to delete or insert using the
Excel menu. I don't want to keep the sheet in a protected mode since this is
too restrictive, but I do need a way to protect the columns. Any help with
this is appreciated.

Disable Menu Items Insert/Delete Entire Column(s)
 

Hello again JR,
Sorry, it was so long ago since I did this that I forgot that the reason for
your error is possibly because you are not deleting your tmpMenuBar if it
exists.

Am I correct in thinking that you are not getting an error message the very
first time you run the code and subsequently you do?.

Anyway add the following to your code just before the SET commands:

For tBars = 1 To CommandBars.Count
If CommandBars(tBars).Name = "tmpMenuBar" Then
CommandBars(tBars).Delete
End If
Next

This should get rid of the error

Chas
"JR_06062005" wrote:

That sounds like a good idea. Do you think you could help me a little with
the code to do this? I created a menu which duplicates the main menu except
under Edit the Delete option has been removed and under Insert the Columns
has been removed. The menu is called tmpMenuBar.

Dim oldMbar As CommandBar, newMbar As CommandBar
Set oldMbar = CommandBars.ActiveMenuBar
Set newMbar = CommandBars.Add _
(Name:="tmpMenuBar", Position:=msoBarRight, _
MenuBar:=True, temporary:=True)
With newMbar
.Visible = True
.Protection = msoBarNoMove
End With

The message I get when I try to run the code is "Invalid procedure or call."
Since this is a modified call from Microsoft VB Help, I am guessing that I
need to add a library, but have no idea which one.

"ChasAA" wrote:

You could have a custom menu.
Or you could temporarily disable the "Edit" and "Insert" menus from the menu
bar.
As far as I can remember you could be more specific and disable only some of
the sub menus of Edit and Insert.

If this will suit you, let me know and I'll try and look up one of my old
programs as to how I did it.

Chas

"JR_06062005" wrote:

I have a spreadsheet which has some macros associated with it and need to
make sure that columns are not deleted or inserted. For the case where the
context menu is used to delete or insert a columns, the following code works
because one has to select the entire column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Rows.Count = 65536 Then
ActiveSheet.Protect
Else
ActiveSheet.Unprotect
End If
End Sub
(The above code places the spreadsheet in the protection mode if an
entire column is selected and unprotects it when a cell or range of cells is
selected.)

However, this does not work if someone decides to delete or insert using the
Excel menu. I don't want to keep the sheet in a protected mode since this is
too restrictive, but I do need a way to protect the columns. Any help with
this is appreciated.