MrExcel Publishing
Your One Stop for Excel Tips & Solutions

inserting a row

Posted by mark on July 03, 2001 7:26 AM

Is there a way to protect a worksheet but still have the ability to insert rows in certain places?

Posted by Bob on July 03, 2001 12:17 PM

Sort of. What I have done is to have a macro add these rows (a button activates the macro). The macro includes the code necessary to unprotect the sheet and then reprotect the sheet. Trouble here is that the macro needs to know the password and then you need to protect the macro so the user cannot view the macro and thus get the password.

Here is an example:

Sub New_Line()
' New_Line Macro

Application.ScreenUpdating = False
' Turn off the screen.

ActiveSheet.Unprotect Password:="bobbob"
' Unprotect the worksheet.

Selection.Insert Shift:=xlDown
' Insert a new row before row 16. This could be any row and could use a named range in Excel.

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="bobbob"
' Reprotect the sheet.

Application.ScreenUpdating = True
' Turn on the screen.

End Sub