Unprotect sheets using macro

**hsmith** · 02-13-2012, 08:41 AM

I have the below code set in a macro. When I start out with all sheets unprotected everything works fine and the sheets are protected when finished. When I run the macro a second time it is not unprotecting the sheets like its supposed to. Any ideas how to get this to work. I am trying to keep everything in the one macro since I have to update this workbook at least once a month if not more. I don't want to have multiple macros I have to update or check each time I make a change to the workbook.

Sub ComboBox()

 Sheets("Summary-Graphs").Unprotect password:="password"
    
 Sheets("Summary").Unprotect password:="password"
    
 Sheets("YTD Rates").Unprotect password:="password"
 
 Sheets("Cost & Utilization").Unprotect password:="password"
    
 Sheets("Brand vs. Generic").Unprotect password:="password"

 Sheets("Drug Class").Unprotect password:="password"

 Sheets("Opioids").Unprotect password:="password"
    
 Sheets("Control").Unprotect password:="password"
 
 Sheets("Raw Data").Unprotect password:="password"
    
 Sheets("Raw Membership").Unprotect password:="password"
 
     
 Sheets("Cost & Utilization").Select
    
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable2").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable2").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    
    ActiveSheet.PivotTables("PivotTable3").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable3").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable3").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable4").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable4").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable5").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable5").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
   
 Sheets("Brand vs. Generic").Select
    
    ActiveSheet.PivotTables("PivotTable13").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable13").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable13").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    ActiveSheet.PivotTables("PivotTable13").PivotFields("LOB").CurrentPage = Sheets("Control").Range("P1").Value
       
 Sheets("Drug Class").Select
    
    ActiveSheet.PivotTables("PivotTable12").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable12").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable12").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    ActiveSheet.PivotTables("PivotTable12").PivotFields("LOB").CurrentPage = Sheets("Control").Range("P1").Value
       
 Sheets("Opioids").Select
    
    ActiveSheet.PivotTables("PivotTable10").PivotFields("MonthFilled").CurrentPage = Sheets("Control").Range("T1").Value
    ActiveSheet.PivotTables("PivotTable11").PivotFields("MonthFilled").CurrentPage = Sheets("Control").Range("T1").Value
         
 Sheets("Control").Select
    
    ActiveSheet.PivotTables("PivotTable4").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable4").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable4").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
    
    ActiveSheet.PivotTables("PivotTable5").PivotFields("Region").CurrentPage = Sheets("Control").Range("L1").Value
    ActiveSheet.PivotTables("PivotTable5").PivotFields("County").CurrentPage = Sheets("Control").Range("AB1").Value
    ActiveSheet.PivotTables("PivotTable5").PivotFields("LOB").CurrentPage = Sheets("Control").Range("P1").Value
    ActiveSheet.PivotTables("PivotTable5").PivotFields("CenterName").CurrentPage = Sheets("Control").Range("X1").Value
   
 Sheets("Summary-Graphs").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Summary").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("YTD Rates").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Cost & Utilization").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Brand vs. Generic").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Drug Class").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Opioids").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Control").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
    
 Sheets("Raw Data").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Raw Membership").Select
    ActiveSheet.Protect password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True _
    , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
        
 Sheets("Filter Selection").Select

 
End Sub

**arlu1201** · 02-13-2012, 08:51 AM

I tried the code at my end and it works fine.

Also, just an observation. You do not have to repeat the lines for each sheet if your password is the same for all. You can have a loop. Like this -

For i = 1 to worksheets.count
   if worksheets(i).name <>"Test" then
        worksheets(i).Unprotect password:="password"
   endif
next i

Repeat the same for the protect code. In the above example, the unprotect code will not work for the "Test" worksheet.

**hsmith** · 02-13-2012, 08:59 AM

I get an error when the sheets are protected if when I change to what you suggested.
"The cell or chart that you are trying to change is protected and therefore read-only."

**arlu1201** · 02-13-2012, 09:01 AM

Yes, after protection, you will get that error.

**hsmith** · 02-13-2012, 09:05 AM

I have to be able to make this workbook so other users can use the combobox filter multiple times. So it needs when a selection is made in the combobox the sheets need to be unprotected run through and make the changes based on the filter than the sheets need to be protected again. Right now that currently is not happening. If I start with the sheets unprotected everything is fine its when I change the filters a second time I get the error and the changes do not take place. How can I fix this?

**arlu1201** · 02-13-2012, 09:12 AM

Why dont you attach the file that you are using? Strip off all confidential data.

**hsmith** · 02-13-2012, 09:19 AM

By the time I stripped all the confidential information there wouldn't be anything left to attach here.

**OnErrorGoto0** · 02-13-2012, 09:23 AM

It sounds to me as though your passwords don't match in your actual code then (I assume the password is not actually 'password'). It would be much better to declare the password as a constant at the beginning of the code and then refer to the constant in ever line where it is needed. It will also make it much easier to change.

**hsmith** · 02-13-2012, 09:29 AM

The password does match in my actual code I have checked that multiple times. The password is something that will never change for this so I don't see the need to declare it.

**hsmith** · 02-13-2012, 09:54 AM

If I did choose to declare the password as a constant how would I do this? I am fairly new to writing macros in VB and am lucky I have gotten as far as I have on my own. I really need to figure out what the issue is with why it will not unprotect the sheets on the second pass sometime today.

**OnErrorGoto0** · 02-13-2012, 10:16 AM

Try this

Sub ComboBox()
    Const cstrPASSWORD As String = "password"
    Dim varSheets
    Dim varItem
    Dim wsControl As Worksheet
    Dim strRegion As String
    Dim strCounty As String
    Dim strLOB As String
    Dim strCenterName As String
    
    Set wsControl = Sheets("Control")
    With wsControl
        strRegion = .Range("L1").Value
        strCounty = .Range("AB1").Value
        strLOB = .Range("P1").Value
        strCenterName = .Range("X1").Value
    End With

    varSheets = Array("Summary-Graphs", "Summary", "YTD Rates", "Cost & Utilization", "Brand vs. Generic", _
                      "Drug Class", "Opioids", "Control", "Raw Data", "Raw Membership")

    For Each varItem In varSheets
        Sheets(varItem).Unprotect Password:=cstrPASSWORD
    Next varItem


    With Sheets("Cost & Utilization")
        With .PivotTables("PivotTable2")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With
        With .PivotTables("PivotTable3")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With

        With .PivotTables("PivotTable4")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With

        With .PivotTables("PivotTable5")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With
    End With

    With Sheets("Brand vs. Generic").PivotTables("PivotTable13")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("County").CurrentPage = strCounty
        .PivotFields("CenterName").CurrentPage = strCenterName
        .PivotFields("LOB").CurrentPage = strLOB
    End With

    With Sheets("Drug Class").PivotTables("PivotTable12")
        .PivotFields("Region").CurrentPage = strRegion
        .PivotFields("County").CurrentPage = strCounty
        .PivotFields("CenterName").CurrentPage = strCenterName
        .PivotFields("LOB").CurrentPage = strLOB
    End With

    With Sheets("Opioids")
        .PivotTables("PivotTable10").PivotFields("MonthFilled").CurrentPage = wsControl.Range("T1").Value
        .PivotTables("PivotTable11").PivotFields("MonthFilled").CurrentPage = wsControl.Range("T1").Value
    End With

    With Sheets("Control")

        With .PivotTables("PivotTable4")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With

        With .PivotTables("PivotTable5")
            .PivotFields("Region").CurrentPage = strRegion
            .PivotFields("County").CurrentPage = strCounty
            .PivotFields("LOB").CurrentPage = strLOB
            .PivotFields("CenterName").CurrentPage = strCenterName
        End With
    End With

    For Each varItem In varSheets
        Sheets(varItem).Protect Password:=cstrPASSWORD, DrawingObjects:=True, Contents:=True, Scenarios:=True _
                              , AllowFormattingColumns:=True, AllowSorting:=True, AllowFiltering:=True _
                              , AllowUsingPivotTables:=True
    Next varItem

    Sheets("Filter Selection").Select


End Sub

**hsmith** · 02-13-2012, 10:33 AM

Using this code provided it still will not unprotect the sheets.

**OnErrorGoto0** · 02-13-2012, 10:41 AM

So you are getting errors? If so, on which line? I cannot see anything wrong with that code, so would have to suspect the error is elsewhere. Do you have any controls bound directly to the worksheets (eg the Control sheet) that might be trying to update the sheets after they are protected?

**hsmith** · 02-13-2012, 10:48 AM

There are no errors in the code I am getting the attached mesage when the sheets are protected. I have not other place that the sheets are being protectd other than through this macro. When I start with all sheets unprotected everything works great its when I run a filter the second time that I have the problem.

**OnErrorGoto0** · 02-13-2012, 10:57 AM

Then I suspect that your form is trying to change a sheet directly. However, since it is protected, you can't do that. My best guess would be that you are trying to update the Control sheet but as you have protected it, you can't. It may be easiest to have the form unprotect the sheets when it loads and then reprotect them when it is unloaded.