Help with VBA to protect and unprtec sheet

[Anandpersad]

Hi,
I have a file with the following VBA:</SPAN></SPAN>

Private Sub Worksheet_Change(ByVal Target As Range)</SPAN></SPAN>
Dim Sheet As Worksheet</SPAN></SPAN>
'I have a dropdown with the following options:</SPAN></SPAN>
'New > 250 but <750</SPAN></SPAN>
'New >750</SPAN></SPAN>
'Existing >250 but <750</SPAN></SPAN>
'Existing >750</SPAN></SPAN>
'Ending</SPAN></SPAN>

'I have the following named areas:</SPAN></SPAN>
'Pricing: Rows 35 - 49</SPAN></SPAN>
'Company: Rows 49 - 64</SPAN></SPAN>
'Payment: Rows 69 - 73</SPAN></SPAN>
'History: Rows 94 - 103</SPAN></SPAN>
'Vote : Rows 104 - 111</SPAN></SPAN>

With Worksheet("Checklist review")</SPAN></SPAN>
If Target.Address(0, 0) = "H7" Then</SPAN></SPAN>
Application.ScreenUpdating = False</SPAN></SPAN>
Select Case Target.Value</SPAN></SPAN>
Case "New customer: line >€250K but <€750"</SPAN></SPAN>
'hide Pricing, Customer, Payment, History and Vote</SPAN></SPAN>
Range("Pricing").EntireRow.Hidden = True</SPAN></SPAN>
Range("Company").EntireRow.Hidden = False</SPAN></SPAN>
Range("Payment").EntireRow.Hidden = True</SPAN></SPAN>
Range("History").EntireRow.Hidden = True</SPAN></SPAN>
Range("Vote").EntireRow.Hidden = True</SPAN></SPAN>
Range("9:35").EntireRow.Hidden = False</SPAN></SPAN>
Range("65:68").EntireRow.Hidden = False</SPAN></SPAN>
Range("74:93").EntireRow.Hidden = False</SPAN></SPAN>
Range("112:114").EntireRow.Hidden = False
Case "New customer: line >€750K"
'hide Payment and History hidden. Pricing, Customer and Vote unhidden
Range("Pricing").EntireRow.Hidden = False
Range("Company").EntireRow.Hidden = False
Range("Payment").EntireRow.Hidden = True
Range("History").EntireRow.Hidden = True
Range("Vote").EntireRow.Hidden = False
Range("9:34").EntireRow.Hidden = False
Range("65:68").EntireRow.Hidden = False
Range("74:93").EntireRow.Hidden = False
Range("112:114").EntireRow.Hidden = False
End Select
Application.ScreenUpdating = True
End If

This file has two sheets. One is called “Checklist review” and the second one is called “Sales WPO”</SPAN></SPAN>
Sheet “Checklist review” has to be protected, so no changes cannot be made and sheet “Sales” should be fully unprotected.</SPAN>
</SPAN>
To protect the file is have the following VBA:</SPAN></SPAN>
Private Sub Workbook_Open()</SPAN></SPAN>
Dim wSheet As Worksheet</SPAN></SPAN>
For Each wSheet In Worksheet</SPAN></SPAN>
If wSheet.Name = "Sales WPO" Then</SPAN></SPAN>
wSheet.Unprotect</SPAN></SPAN>
'ElseIf wSheet.Protect = True Then</SPAN></SPAN>
'wSheet.Unprotect Password:="Secret", _</SPAN></SPAN>
'UserInterFaceOnly:=True</SPAN></SPAN>
Else</SPAN></SPAN>
wSheet.Protect Password:="Secret", _</SPAN></SPAN>
UserInterFaceOnly:=True</SPAN></SPAN>
End If</SPAN></SPAN>
Next wSheet</SPAN></SPAN>
End Sub</SPAN></SPAN>

So far so good.The problem appears when I choose for one of the options in the dropdown list.</SPAN></SPAN>
That is not working anymore. It redirects to the VBA .
The option as choosen from the drop down, highlights yellow in the VBA
</SPAN></SPAN>
If I manually unprotect the “Checklist review” the VBA works fine again.</SPAN></SPAN>
Can someone please help me with this. I cannot find the bug, causing this problem.


Thanks in advance,

Anandpersad</SPAN></SPAN>

 

[Aaron Blood]

For the sake of running a macro... you should never unprotect/reprotect in code. Rather, the Protect method includes a UserInterfaceOnly option that should be set to TRUE. ...which then allows your macros to run unhindered on protected sheets. Aside from bogging down code with constant protect/reprotect routines and having to include your PW in the code, you also avoid the risk of the macro encountering an error and exiting before it reprotects the sheet.

Somewhere in the initialize phase you just need to have your workbook protect the sheet with code something like: Sheet1.Protect Password:="secret", UserInterfaceOnly:=True