Sheet Protection problem

Hi all,

This code does every thing i want it to, even though its not written well.
The problem is if i use the line:

'====> ActiveSheet.Protect 'Password:="12345"

at the end, then the code hangs up, and i don't understand why as the code should be
done running when the sheet is re-protected.

If i don't use the line to reprotect the sheet then the code does what its supose to without eror.

Error is --> "Unable to set the lineStyle Property of the Border Class"
and its on the first ".LineStyle = xlDash" line.

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)
    End If
    ''''''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 Sub

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