CookieMonster76
Board Regular
- Joined
- Apr 30, 2015
- Messages
- 195
Hi
I have picked this up that someone else has created, and it gives the result it is designed to do (I don't know if it's the best way to do it, but i'm ignoring that for now). It runs off a drop down menu, so if you select Village1 from the list, it refreshes the pivot table and returns data relating to Village 1.
My issue is that I have been asked to password protect the sheet, which i have done. However, as the cells become locked, the code fails. So i have added in the 2 red lines, to basically unprotect it, do what it needs to do, and then re-protect it. However, it isn't unprotecting it, and then fails at the point it tries to refresh the pivot table.
Any advice greatly appreciated.
Thanks
Paul
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="safe"
Dim KeyCells As Range
Set KeyCells = Range("C4")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
chosenvillage = Range("c4").Value
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems("Village1").Visible = True
.PivotItems("Village2").Visible = False
.PivotItems("Village3").Visible = False
.PivotItems("Village4").Visible = False
End With
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems(chosenvillage).Visible = True
End With
If chosenvillage = "Village1" Then GoTo HERE
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems("Village1").Visible = False
End With
HERE:
' Sheets("AC").Select
' ActiveSheet.ListObjects("AC").Range.AutoFilter Field:=1
' ActiveSheet.ListObjects("AC").Range.AutoFilter Field:=1, Criteria1:= _
' chosenvillage
' Sheets("Contract Mgt Portal").Select
End If
ActiveSheet.Protect Password:="safe"
End Sub
I have picked this up that someone else has created, and it gives the result it is designed to do (I don't know if it's the best way to do it, but i'm ignoring that for now). It runs off a drop down menu, so if you select Village1 from the list, it refreshes the pivot table and returns data relating to Village 1.
My issue is that I have been asked to password protect the sheet, which i have done. However, as the cells become locked, the code fails. So i have added in the 2 red lines, to basically unprotect it, do what it needs to do, and then re-protect it. However, it isn't unprotecting it, and then fails at the point it tries to refresh the pivot table.
Any advice greatly appreciated.
Thanks
Paul
Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect Password:="safe"
Dim KeyCells As Range
Set KeyCells = Range("C4")
If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
chosenvillage = Range("c4").Value
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems("Village1").Visible = True
.PivotItems("Village2").Visible = False
.PivotItems("Village3").Visible = False
.PivotItems("Village4").Visible = False
End With
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems(chosenvillage).Visible = True
End With
If chosenvillage = "Village1" Then GoTo HERE
With ActiveSheet.PivotTables("ServiceProvisions").PivotFields("LocationName")
.PivotItems("Village1").Visible = False
End With
HERE:
' Sheets("AC").Select
' ActiveSheet.ListObjects("AC").Range.AutoFilter Field:=1
' ActiveSheet.ListObjects("AC").Range.AutoFilter Field:=1, Criteria1:= _
' chosenvillage
' Sheets("Contract Mgt Portal").Select
End If
ActiveSheet.Protect Password:="safe"
End Sub