Is it wrong to have two to three worksheet lock up?

[DavidRoger]

Hi,

Currently I have code for sheet protection after certain time, lock formula and block all copy paste.

I tested all the code separately. When I put all together, sheet protection doesn't work. Do a lot of mixed and test but not working either.

Are there rules that we cant have multiple sheet lock up?

 

[Smitty]

You shouldn't have a problem, but it would help to post your code (the relevant bits anyway).

 

[DavidRoger]

In the ThisWorkbook

Private Sub Workbook_Open()   <---sheet protection
Application.OnTime Now() > DateValue("5/22/2015"), "Protectsheets"
End Sub

Private Sub Workbook_Activate()   <--- here and below block all copy paste
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_Deactivate()
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Application.CutCopyMode = False
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Application.CellDragAndDrop = True
Application.OnKey "^c"
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
'MsgBox "Right click menu deactivated." & vbCrLf & _
'"Cannot copy or ''drag & drop''.", 16, "For this workbook:"
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Application.OnKey "^c", ""
Application.CellDragAndDrop = False
Application.CutCopyMode = False
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

In the regular module:

Sub Protectsheets()  <---sheet protection
Dim Sh As Worksheet
Dim myPassword As String
myPassword = "password"

For Each Sh In ActiveWorkbook.Worksheets
Sh.Protect Password:=myPassword
Next Sh

End Sub

Sub lockformula()  <--- lock formula, has to run every time new formula added at every sheet

With ActiveSheet
.Unprotect Password:="justme"
.Cells.Locked = False
.EnableSelection = xlUnlockedCells
.UsedRange.Select
End With
For Each cell In Selection
If cell.HasFormula Then
cell.Locked = True
Selection.FormulaHidden = False
End If
Next
Range("A1").Select
ActiveSheet.Protect Password:="justme"

Is there a way to ammend the tile of password pop up to know which one is locked?