choubix
05-22-2008, 04:57 AM
hello,
I am protecting the workbook and the worksheets in workbook_open()
then I have a sub that unprotects cells of a certain color (found the code here! :) )
are the subs in "ThisWorkbook" / Workbook automatically triggered when the workbook is opened?
OR:
do I need to add the UnprotectGreenCells() to workbook_open() so that it is triggered autoamtically when the workbook is open?
here is the code :)
Private Sub Workbook_Open()
'protect the sheets
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
'protects the workbook: users can't delete/move/add... worksheets
ActiveWorkbook.Protect _
Structure:=True, _
Windows:=False
End Sub
Sub UnprotectGreenCells()
'Macro purpose: To unlock all green cells
'NOTE: All worksheets in the workbook must be
' unprotected before this procedure is run
Dim cl As Range, ws As Worksheet, lColor As Long
'Set the cell color that you want to protect
lColor = 36 'yellow
'It may be a good idea to test all sheets to see if any are protected
'here. One method to do this would be to test the function found here
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=551
'Cycle through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
For Each cl In ws.UsedRange
'Change colored cell to unlocked, and
'all other cells to locked
If cl.Interior.ColorIndex = lColor Then
cl.Locked = False
Else
cl.Locked = True
End If
Next cl
Next ws
End Sub
thank you
I am protecting the workbook and the worksheets in workbook_open()
then I have a sub that unprotects cells of a certain color (found the code here! :) )
are the subs in "ThisWorkbook" / Workbook automatically triggered when the workbook is opened?
OR:
do I need to add the UnprotectGreenCells() to workbook_open() so that it is triggered autoamtically when the workbook is open?
here is the code :)
Private Sub Workbook_Open()
'protect the sheets
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.Protect Password:="Secret", _
UserInterFaceOnly:=True
Next wSheet
'protects the workbook: users can't delete/move/add... worksheets
ActiveWorkbook.Protect _
Structure:=True, _
Windows:=False
End Sub
Sub UnprotectGreenCells()
'Macro purpose: To unlock all green cells
'NOTE: All worksheets in the workbook must be
' unprotected before this procedure is run
Dim cl As Range, ws As Worksheet, lColor As Long
'Set the cell color that you want to protect
lColor = 36 'yellow
'It may be a good idea to test all sheets to see if any are protected
'here. One method to do this would be to test the function found here
'http://www.vbaexpress.com/kb/getarticle.php?kb_id=551
'Cycle through each worksheet in the workbook
For Each ws In ActiveWorkbook.Worksheets
For Each cl In ws.UsedRange
'Change colored cell to unlocked, and
'all other cells to locked
If cl.Interior.ColorIndex = lColor Then
cl.Locked = False
Else
cl.Locked = True
End If
Next cl
Next ws
End Sub
thank you