Solved: Protect worksheet / Worksheet event

[Shazam]

Ok I put this code together and it works great. But how can the code be modified to work when the worksheet is protected?



[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Dim rng As Range
If Target.Count > 1 Then Exit Sub
' Set the target Range
Set rng = Range("C:C")
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
If Target.Count > 2 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
Target.Offset(0, 2).FillDown 'E
Target.Offset(0, 7).FillDown 'K
Target.Offset(0, 8).FillDown 'L
Target.Offset(0, 10).FillDown 'M
GetOut:
End Sub
[/VBA]

[Shazam]

Never mind I got it.


[VBA] Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo GetOut
Application.DisplayAlerts = False
Dim rng As Range
If Target.Count > 1 Then Exit Sub
' Set the target Range
Set rng = Range("C:C")
' Only look at that range
Worksheets("SYSTEM 1").Unprotect Password:="123456"
'Do your code here

If Intersect(Target, rng) Is Nothing Then Exit Sub

If Target.Count > 2 Then Exit Sub
If Target.Column <> 3 Then Exit Sub
Target.Offset(0, 2).FillDown 'E
Target.Offset(0, 7).FillDown 'K
Target.Offset(0, 8).FillDown 'L
Target.Offset(0, 10).FillDown 'M
Worksheets("SYSTEM 1").Protect Password:="123456"

Application.DisplayAlerts = True
GetOut:
[/VBA]

[mvidas]

If I'm working with a workbook that could have protected sheets, I usually put something in workbook_open to unprotect then reprotect with user interface only=true, so event code like yours above doesnt have to unprotect/reprotect at every trigger

[Shazam]

Thank you for replying,

The workbook that I'm working on stays open all day thru the night we here are open 24 hours. Another question the line code you see below fills down the cell how can it fill down just the format in the cell?

[VBA] Target.Offset(0, 8).FillDown[/VBA]

[mvidas]

If it stays open that is fine, the UserInterfaceOnly argument (when true) just protects the sheet from the user, but not from VBA. Code can run as freely as you want, but the user still has limitations. The reason I mentioned putting it in workbook_open is because it has to be protected as userinterfaceonly=true once per workbook session (each time its opened it loses the UIO=true portion of the protection, an intentional safety feature).

Unfortunately I don't believe the .FillDown feature works only with formats.. but pastespecial does[vba] With Target.Offset(0, 8)
.Offset(-1, 0).Copy
.PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False[/vba]Matt

[Shazam]

If it stays open that is fine, the UserInterfaceOnly argument (when true) just protects the sheet from the user, but not from VBA. Code can run as freely as you want, but the user still has limitations. The reason I mentioned putting it in workbook_open is because it has to be protected as userinterfaceonly=true once per workbook session (each time its opened it loses the UIO=true portion of the protection, an intentional safety feature).

Unfortunately I don't believe the .FillDown feature works only with formats.. but pastespecial does[vba] With Target.Offset(0, 8)
.Offset(-1, 0).Copy
.PasteSpecial xlPasteFormats
End With
Application.CutCopyMode = False[/vba]Matt

Thanks it works perfect. also I will try to see if I could modify the workbook what you quoted.

Thank You