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.

Suggest you post this the Excel newsgroup.

--
==============================================
Thomas A. Rowe
Microsoft MVP - FrontPage
==============================================
Agents Real Estate Listing Network
http://www.NReal.com
==============================================


"JR_06062005" <(E-Mail Removed)> wrote in message
news:E759DE5F-896E-4F2E-99E2-(E-Mail Removed)...
>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.

Sorry about that.

"Thomas A. Rowe" wrote:

> Suggest you post this the Excel newsgroup.
>
> --
> ==============================================
> Thomas A. Rowe
> Microsoft MVP - FrontPage
> ==============================================
> Agents Real Estate Listing Network
> http://www.NReal.com
> ==============================================
>
>
> "JR_06062005" <(E-Mail Removed)> wrote in message
> news:E759DE5F-896E-4F2E-99E2-(E-Mail Removed)...
> >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.
>
>
>