Protecting Worksheet bugs my Macro

[jscranton]

I have a worksheet for about 50 users that I want to protect. They are using it for data entry and I am trying to several limit the amount of free typing.

THat also have to insert rows occassionally. With some help from the board, I have a macro that automatically inserts a new row and auto-numbers it. HOwever, when I protect the worksheet--even if I allow people the right to insert rows--my Macro no longer works.

I basically want to protect my sheet so the format, data validation, etc are preserved.


Sub InsertRow()
Dim a As Integer
Dim X As String
X = ActiveCell.Value
If Len(X) = 7 Then GoTo NoCountNumber:
On Error GoTo NoCountNumber:
a = CInt(Right(X, 2))
If a > 0 And a < 9 Then
a = a + 1
With ActiveCell
.EntireRow.Copy
.Offset(1, 0).EntireRow.Insert Shift:=xlDown
.Offset(1, 0).Value = Left(X, Len(X) - 2) & CStr("0" & a)
End With
Application.CutCopyMode = False

Else
a = a + 1
With ActiveCell
.EntireRow.Copy
.Offset(1, 0).EntireRow.Insert Shift:=xlDown
.Offset(1, 0).Value = Left(X, Len(X) - 2) & CStr(a)
End With


End If
GoTo Finish:
NoCountNumber:
With ActiveCell
.EntireRow.Copy
.Offset(1, 0).EntireRow.Insert Shift:=xlDown
.Offset(1, 0).Value = X & "_01"
End With
Application.CutCopyMode = False
Finish:
End Sub

 

[Andrew Poulsom]

Why don't you unprotect the sheet at the beginning of your procedure and protect it again at the end? To insert copied cells on a protected sheet the cells that are being copied must be unlocked.

 

[AlphaFrog]

Put this at the start of your macro.

ActiveSheet.Protect Password:="[COLOR="Red"]Secret[/COLOR]", UserInterFaceOnly:=True

It keeps the sheet protected but allows the macro to alter to the sheet. Change the password to suit.

 

[jscranton]

THe sheet will work like a template for about 50 people. I want to protect it, send it to them and keep the sheet as protected as I can from them. (this is not an excel-savvy team).

The macro addition worked great. Macros are running but now I have realized that folks can't delete an inserted row since when the Macro inserts the row it also inserts locked cells. I have given them access to delete a row on the protection wizard but it doesn't help.

Any thoughts?