Help with VBA to protect and unprtec sheet

Anandpersad

New Member
Joined
Oct 18, 2014
Messages
31
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>
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Aaron Blood

Active Member
Joined
Oct 10, 2002
Messages
485
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,353
Messages
5,595,666
Members
414,007
Latest member
bongda360org

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top