Hi I am using the following macro to insert rows into a protected spreadsheet. It all works fine unless the user hits cancel on the popup of how many rows they want to insert, I have tried various combinations but can't get it to protect the spread sheet after they cancel or if it does it then doesn't protect it when they do insert rows....
Sub InsertRowsAndFillFormulas_caller() '-- this macro shows on Tools, Macro..., Macros (Alt+F8) dialog Call InsertRowsAndFillFormulas End Sub Sub InsertRowsAndFillFormulas(Optional vRows As Long = 0) ActiveSheet.Unprotect Password:="password" ' Documented: [url]http://www.mvps.org/dmcritchie/excel/insrtrow.htm[/url] ' Re: Insert Rows -- 1997/09/24 Mark Hill <[email protected]> ' row selection based on active cell -- rev. 2000-09-02 David McRitchie Dim x As Long ActiveCell.EntireRow.Select 'So you do not have to preselect entire row If vRows = 0 Then vRows = Application.InputBox(prompt:= _ "How many rows do you want to add?", Title:="Add Rows", _ Default:=1, Type:=1) 'Default for 1 row, type 1 is number If vRows = False Then Exit Sub End If 'if you just want to add cells and not entire rows 'then delete ".EntireRow" in the following line 'rev. 2001-01-17 Gary L. Brown, programming, Grouped sheets Dim sht As Worksheet, shts() As String, i As Long ReDim shts(1 To Worksheets.Application.ActiveWorkbook. _ Windows(1).SelectedSheets.Count) i = 0 For Each sht In _ Application.ActiveWorkbook.Windows(1).SelectedSheets Sheets(sht.Name).Select i = i + 1 shts(i) = sht.Name x = Sheets(sht.Name).UsedRange.Rows.Count 'lastcell fixup Selection.Resize(rowsize:=2).Rows(2).EntireRow. _ Resize(rowsize:=vRows).Insert Shift:=xlDown Selection.AutoFill Selection.Resize( _ rowsize:=vRows + 1), xlFillDefault On Error Resume Next Selection.Offset(1).Resize(vRows).EntireRow. _ SpecialCells(xlConstants).ClearContents Next sht Worksheets(shts).Select ActiveSheet.Protect Password:="password", AllowDeletingRows:=True, DrawingObjects:=True, _ Contents:=True, Scenarios:=True End Sub