outling and protected worksheets and pivot table updating.

[AUTO]

Hi Folks,

Sorry for the long topic heading but...

I'm trying to produce a timesheet/invoicing system and all is going well except for this one problem. I know NOTHING about vba and I have two small bits of code picked up off the web.

1. Some code to enable outlining in a protected sheet.

Here it is:

[pre]

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Protect Password:="les", UserInterfaceOnly:=True
.EnableOutlining = True
End With
Next ws
End Sub
2. Code to update a pivot table.

Here it is:

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Monthly Sorted").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

[/pre]
Code1 is in "This workbook"

Code2 is in 5 separate sheets.

I don't know if this is where they're supposed to reside. Anyway, my problem is that the sheet containing the pivot table is protected by the first piece of code which means it won't update.

I need to ensure the pivot table is not protected so it will update properly.

I hope I've made this clear.

Help from the VBA folk would be great just remember it will have to be idiot proof

TIA

Shalom

Auto

 

[Luke M]

Need to unprotect the PivotTable first. Perhaps changing the 2nd macro to this:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Monthly Sorted").Unprotect "les"
Worksheets("Monthly Sorted").PivotTables("PivotTable1").PivotCache.Refresh
Worksheets("Monthly Sorted").Protect "les"
End Sub

[/pre]

 

[AUTO]

Hi Luke,

Thanks for the reply.

I've changed the macro but, no good.

I still get an error pop-up saying "cannot edit pivot table on protected sheet"

Another try?

Auto

 

[AUTO]

I've just thought.

There is no need to protect the "Monthly Sorted" sheet.

Does that make any difference?

Auto

 

[Luke M]

We can make an exception clause in the first macro then.

[pre]

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> "Monthly Sorted" Then
With ws
.Protect Password:="les", UserInterfaceOnly:=True
.EnableOutlining = True
End With
End If
Next ws
End Sub

[/pre]
You'll need to (manually) unprotect the worksheet the first time.

 

[AUTO]

Luke, you the MAN!

Works a treat.

Thanks.

Auto

 

[Luke M]

You're very welcome. =)