MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Restricting ability to add or delete columns and rows


Posted by Bill Mahoney on May 18, 2001 2:00 PM

Hello, is there a way to restrict people from adding or deleting columns and rows while still allowing them to resize or hide columns and rows?

Thanks for your time and assistance.

Bill Mahoney


Posted by Ivan Moala on May 19, 2001 12:34 AM

Bill here is a method I use based on
David Hagers (Give credit to him)

In the sheet you want to protect place this code
in the sheets code page (right click sheets tab & select view code)
Public bflag As Boolean

Private Sub Worksheet_Calculate()
Static bflag

If bflag Then Exit Sub

On Error Resume Next
With Application
.EnableEvents = False
.Undo
.EnableEvents = True
End With
End Sub

Then in add a module and place this code in;

Sub ChangeProtectMode()
Static bflag

Dim PW As String
'Run this routine again to Protect the sheet 'IFM
If bflag Then
bflag = False
Range("IV1").FormulaR1C1 = "=COUNTA(R[1]C:R[65535]C,C[-255]:C[-1])"
Exit Sub
Else
PW = InputBox("Password......")
If PW <> "test" Then End
'Replace formula
Range("IV1") = 1
bflag = True
End If
End Sub

This code will allow you to edit the sheet if you
need to. Password = "test" change it to suit.
Attach the code to an object or button.
Now it will allow you to do anything that doesn't require calculating, hence the use of the worksheet calculation event handler.

If you require a workbook example to email me.


Ivan