Inserting rows in protected sheet

All worksheets should be protected from the prying fingers of end users, but this causes a problem when you want to programatically make changes.

What I do is I have two subroutines Lock_Sheet and Unlock_Sheet:

'***************************************************************************
'*
'* Sub:      Lock_Sheet
'*
'* Purpose:  Locks the sheet for changes
'*
'* Entry:
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Lock_Sheet()
    StackPtr = StackPtr - 1
    Call Set_Changing(Change_Stack(StackPtr).Changing)
    Application.ScreenUpdating = Change_Stack(StackPtr).Update
    Worksheets(Change_Stack(StackPtr).Sheet_Name).Protect DrawingObjects:=Change_Stack(StackPtr).Update, Contents:=Change_Stack(StackPtr).Update, Scenarios:=Change_Stack(StackPtr).Update
    Application.EnableEvents = Change_Stack(StackPtr).Update
    Application.DisplayAlerts = Change_Stack(StackPtr).Update
    If Change_Stack(StackPtr).Update Then
       Application.Calculation = xlCalculationAutomatic
    Else
       Application.Calculation = xlCalculationManual
    End If
    Call Pop_Status
End Sub

'*
'***************************************************************************
'*
'* Sub:      Unlock_Sheet
'*
'* Purpose:  Flags the fact that the sheet is being changed programatically
'*           so that events do not get triggered by the changes
'*
'* Entry:    Name of sheet to unlock or blank for current sheet
'*
'* Exit:
'*
'* Calls:
'*
'***************************************************************************
'*
Sub Unlock_Sheet(ByVal Sheet_Name As String)
    Change_Stack(StackPtr).Sheet_Name = IIf(Sheet_Name = "", ActiveSheet.Name, Sheet_Name)
    Change_Stack(StackPtr).Changing = Changing()
    Change_Stack(StackPtr).Update = Application.ScreenUpdating
    StackPtr = StackPtr + 1
    Call Set_Changing(True)
    If Sheet_Name = "" Then
       ActiveSheet.Unprotect
    Else
       Worksheets(Sheet_Name).Unprotect
    End If
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Call Push_Status("Busy")
End Sub

The actual statement that does the work is

Worksheets(Change_Stack(StackPtr).Sheet_Name).Protect DrawingObjects:=Change_Stack(StackPtr).Update, Contents:=Change_Stack(StackPtr).Update, Scenarios:=Change_Stack(StackPtr).Update

and

Worksheets(Sheet_Name).Unprotect

However, there are a lot of other things to consider as well such as:

• Process events
• Process Calculations
• Display changes as they happen or not
• Display alerts that might be triggered or not

That's why there is a hell of a lot more code surrounding the relevant statements above.

I tend to use a push down stack to store everything that I need to keep track of such as the current state of the worksheet, messages being displayed to the user etc. In that way I can call these routines as many times as I like as long as the calls happen in pairs.

An example of how to do this is below:

    Call Unlock_Sheet("")
    ActiveSheet.Bottom_F = ActiveSheet.Top_B + Val(ActiveSheet.Thickness)
    Call Lock_Sheet

Regards

Rich