Protecting / Uprotecting using VBA

[finbar06]

I am trying to get this Excel workbook to uprotect certain sheets, hide the rows etc, based on a dropdown box and then go back to being protected. This drop down box sets up the workbook and how it looks before anything is typed into it.

If I change the document so the drop down box sits as blank, unprotect the 2 sheets i am working and save it. When you open it, I can select an option and it goes through the process and protects the document everything works fine. If you then want to change the option from 2 to 3 to get it to do the other if statement, it gives you the error message about the sheet being protected and it seems to ignore the fact i have asked it to unprotect the sheets.

I have tried about 10 or so ways of doing it but it's driving me *******s. Can anyone help.


Here's the code

Sub DropDown1_Change()

If Range("P1").Value = "2" Then
Sheets("APPLICATION DETAILS").Select
ActiveSheet.Unprotect
Rows("40:59").Select
Selection.EntireRow.Hidden = True
Rows("79:93").Select
Selection.EntireRow.Hidden = True
Sheets("DECISION").Select
ActiveSheet.Unprotect
Rows("11:58").Select
Selection.EntireRow.Hidden = True
Rows("59:75").Select
Selection.EntireRow.Hidden = False
Range("A59").Select
ActiveSheet.Shapes("Button 37").Visible = False
Sheet6.Visible = False
Sheets("DECISION").Select
ActiveSheet.Protect
Sheets("APPLICATION DETAILS").Select
ActiveSheet.Protect
Range("c10").Select

'MsgBox "To change Appplication type more than once. Please unprotect sheets :Application Details + Decision" _
' , vbInformation + vbOKOnly, "Information"

ElseIf Range("p1").Value = "3" Then

Sheets("DECISION").Select
ActiveSheet.Unprotect
Sheets("APPLICATION DETAILS").Select
ActiveSheet.Unprotect
Rows("40:59").Select
Selection.EntireRow.Hidden = False
Rows("79:93").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Shapes("Button 37").Visible = True
Rows("11:58").Select
Selection.EntireRow.Hidden = False
Rows("59:75").Select
Selection.EntireRow.Hidden = True
Range("a11").Select
Sheet6.Visible = True
Sheets("DECISION").Select
ActiveSheet.Protect
Sheets("APPLICATION DETAILS").Select
ActiveSheet.Protect
Range("c10").Select
'MsgBox "To change Appplication type more than once. Please unprotect sheets :Application Details + Decision" _
' , vbInformation + vbOKOnly, "Information"



End If

End Sub

Message boxes are remaked out just now, but there if I just need to admit defeat.

PS I know the easiest way is just for everyone to open the saved workbook I have, select the option they want and it will work, if you then need to change the option, close it down and reopen it would be fine. I just assumed if you needed to change the option you would be able to.


PPS I have tried a few of the options I have found when searching bu to no avail. I am using Excel 2000
TIA

 

[GlennUK]

....and protects the document everything works fine. If you then want to change the option from 2 to 3 to get it to do the other if statement, it gives you the error message about the sheet being protected .....

So, is this drop-down unprotected? What type of drop-down? An in-cell Data Validation one? A Forms toolbar? Or what? If I was using an in-cell drop-down to choose an option, I'd make sure the cell was formatted as unlocked so that I could enter a choice when the sheet is protected.

 

[finbar06]

D'oh got it now.

The drop down box is one taken from the forms toolbar, it's not locked. It has 3 rows as a control source and a cell link to P1.

so for example

a1 has blank
a2 oranges
a3 apples

this returns "2" in cell P1 if oranges is selected
and "3" in P1 if apples. This is where the routine gets started from.

I've just got where I was going wrong. !!!!! reading the last part of your post reminded me i hadn't unlocked the P1 cell, to allow the data to be changed. AAAARRRRGGHHH !!!

Cheers for Advice. Im sure you've every idea how annoying it was becoming..

Thanks again.

 

[GlennUK]

Ha ha, yes, I've a very good idea of how annoying these things can become.

My pleasure

 

[Smitty]

Note that you can eliminate all of that selecting (not tested):

Sub DropDown1_Change()

    If Range("P1").Value = "2" Then
        With Sheets("APPLICATION DETAILS")
            .Unprotect
                .Rows("40:59").EntireRow.Hidden = True
                .Rows("79:93").EntireRow.Hidden = True
            .Protect
        End With
        With Sheets("DECISION")
            .Unprotect
                .Rows("11:58").EntireRow.Hidden = True
                .Rows("59:75").EntireRow.Hidden = False
                .Shapes("Button 37").Visible = False
            .Protect
        End With
        Sheet6.Visible = False
        
        'MsgBox "To change Appplication type more than once. Please unprotect sheets :Application Details + Decision" _
        ' , vbInformation + vbOKOnly, "Information"
    
    ElseIf Range("p1").Value = "3" Then
        With Sheets("APPLICATION DETAILS")
            .Unprotect
                .Rows("40:59").EntireRow.Hidden = False
                .Rows("79:93").EntireRow.Hidden = False
                .Shapes("Button 37").Visible = True
                .Rows("11:58").EntireRow.Hidden = False
                .Rows("59:75").EntireRow.Hidden = True
            .Protect
        End With
        Sheet6.Visible = True
    
    'MsgBox "To change Appplication type more than once. Please unprotect sheets :Application Details + Decision" _
    ' , vbInformation + vbOKOnly, "Information"
    End If

End Sub

HTH,

Smitty