-
Detect an unprotected worksheet automatically
I'm mucking around with a corporate template that sends a CDO message (bypassing Outlook) if a user attempts to unprotect a worksheet
I could muck around with the menu settings and disable the Protect options but this is more fun
Is there a smart way to detect an unprotected sheet - I'm using the code below but I was wondering if there was a smarter way (class module?) to detect an unprotected worksheet
[vba]
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Dim ws As Worksheet, NotProt As Boolean
For Each ws In ThisWorkbook.Worksheets
If ws.ProtectContents = False Then
NotProt = True
Exit For
End If
Next
If NotProt Then Mail_Small_Text_CDO
End Sub
[/vba]
Cheers
Dave
-
Hi Dave,
The way to use classes in this instance is to wrap a class around a Worksheet variable declared WithEvents and set up a collection to hold the class instances.
The collection can be populated with the Workbook_Open and NewSheet events, then you can write your class event code to handle any worksheet... I hope I explained that ok
For example, the class (called clsSheet) would look like this:[VBA]Option Explicit
Private WithEvents m_ws As Worksheet
Public Sub Init(Sh As Object)
Set m_ws = Sh
End Sub
Private Sub Class_Terminate()
Set m_ws = Nothing
End Sub
Private Sub m_ws_Calculate()
If m_ws.ProtectContents = False Then
MsgBox m_ws.Name & " not protected"
End If
End Sub[/VBA]and the Workbook code would be this:[VBA]Option Explicit
Private colSheets As New Collection
Private wsSheet As clsSheet
Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
Set wsSheet = New clsSheet
wsSheet.Init ws
colSheets.Add wsSheet
Next
End Sub
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Set wsSheet = New clsSheet
wsSheet.Init Sh
colSheets.Add wsSheet
End Sub[/VBA]
K :-)
-
Dave,
I'm thinking why not be both sneeky and proactive here? - I'm assuming the VBE window is protected of course...[vba]Option Explicit
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
For Each Sh In ThisWorkbook.Worksheets
If Sh.ProtectContents = False Then
Mail_Small_Text_CDO
With Application
.EnableEvents = False
On Error Resume Next
.Undo '< undo any changes
.EnableEvents = True
End With
'set a NEW password here
Sh.Protect password:="123"
Exit For
End If
Next
End Sub[/vba]
(edited to insert basic error handler)
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules