Sheet Protection problem

Hi mudraker & Leith,

Thank you so much for your input. Its working, with your sugestions.

Thx again,
Dave

Originally Posted by mudraker

It is because you are making changes to the sheet - Theses change trigger a new worksheet change event which causes the macro to run again.

Use Application.EnableEvents=False at the start of the macro & Application.EnableEvents=True at the end.

Note if the macro fails to get to the Application.EnableEvents=True command for any reason then any more changes to the sheet will not trigger the macro. If this occurs you will need to have another normal macro on the same sheet and run it manually.


Code:
Sub ReEnable EventTriggers
Application.EnableEvents=True
End Sub

Originally Posted by Leith Ross

Hello Desert Piranha,

The problem is once the worksheet is protected, the Worksheet_Change event is disabled and your code never runs. I modified your code a little and it runs. However I am not certain it runs the way you want it to. Here is the code...

Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.Unprotect 'Password:="12345"
    Dim LRow As Long
    '''''Auto number in Column A
    If Not Application.Intersect(Range("b:b"), Target) Is Nothing Then
        Target.Offset(0, -1).Value = "=(Row() - 4)"
    End If
    '''''Autofill the formulas
    If Target.Column = 2 Then
        LRow = Cells(Rows.Count, 2).End(xlUp).Row
        Range("BG5").AutoFill Destination:=Range("BG5:BG" & LRow)
        Range("BH5").AutoFill Destination:=Range("BH5:BH" & LRow)
        Range("BI5").AutoFill Destination:=Range("BI5:BI" & LRow)
        Range("BJ5").AutoFill Destination:=Range("BJ5:BJ" & LRow)
    ''''''Borders down to last used cell in Column B
      LRow = Cells(Rows.Count, "b").End(xlUp).Row
        With Range("A" & LRow, "BJ" & LRow)
        
          With .Borders(xlEdgeRight)
            .LineStyle = xlDash
            .Weight = xlThin
            .ColorIndex = xlAutomatic
          End With
          With .Borders(xlInsideVertical)
            .LineStyle = xlDash
            .Weight = xlThin
            .ColorIndex = xlAutomatic
          End With
          With .Borders(xlEdgeLeft)
            .LineStyle = xlDash
            .Weight = xlThin
            .ColorIndex = xlAutomatic
          End With
        End With
      ActiveSheet.Protect 'Password:="12345"
    End If
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 ActiveSheet.Unprotect 'Password:="12345"
End Sub

Sincerely,
Leith Ross