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....
Code
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