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
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