Correct Format of VBA Code

[luke.walker]

Hi,

I have had a problem recently by where I am trying to protect sheets in a workbook (7 sheets = 1 Data table and 6 with pivot tables/charts). I am looking to achieve the following:

•Protect the sheets so that others can not edit the data (leaving a selected cell range, preferably password protected, available for data input)
•Once data is inputted and a particular sheet (that houses a pivot table/chart) is opened then that pivot automatically refreshes without compromising the protection of the sheet
•If a macro is required to unprotect the sheets before refresh, then an automatic protection of the sheets occurs.


I am new to VBA so after days of sifting through forums, watching videos and so forth I have made some slight progress. However it is becoming tiring, difficult and time consuming as I am running out of time to do this for work.

I have two codes; firstly I have managed to produce a code to protect the workbook upon opening and being protected through the control of a button - code as follows:

Module1


Code:
Sub Protect_All()
Dim ws As Worksheet
'turn screen updating off
Application.ScreenUpdating = False
'loop through each sheet and protect with a password
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="1", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, UserInterfaceOnly:=True
Next ws
'turn screen updating on
Application.ScreenUpdating = True
End Sub
Sub Unprotect_All()
Dim ws As Worksheet
'remove error handling
On Error Resume Next
'turn screen updating off
Application.ScreenUpdating = False
'loop through sheets and unprotect with a password
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="1"
Next ws
'turn screen updating on
Application.ScreenUpdating = True
'reset error handler
On Error GoTo 0
End Sub
ThisWorkbook


Code:
Private Sub Workbook_Open()
Protect_All
End Sub
The second version I have of the workbook I have a code by where upon opening the sheets are protected. Following this code I have an automatic refresh - the problem is when the sheets are protected I get a run time error, but if the sheets are unprotected, the sheets housing pivots refresh automatically and data is pulled across no problem. The relevant sheet then protects but I can not get this to happen for all sheets (I do not know how to develop the whole code). Code as follows:

Module 1


Code:
Sub Protect_All()
Dim ws As Worksheet
'turn screen updating off
Application.ScreenUpdating = False
'loop through each sheet and protect with a password
For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="1", DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True, UserInterfaceOnly:=True
Next ws
'turn screen updating on
Application.ScreenUpdating = True
End Sub
Sub Unprotect_All()
Dim ws As Worksheet
'remove error handling
On Error Resume Next
'turn screen updating off
Application.ScreenUpdating = False
'loop through sheets and unprotect with a password
For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="1"
Next ws
'turn screen updating on
Application.ScreenUpdating = True
'reset error handler
On Error GoTo 0
End Sub
Sub Refresh()
Dim ws As Worksheet
DoNotInclude = "Sheet1"
For Each s In ActiveWorkbook.Worksheets
If InStr(DoNotInclude, s.Name) = 0 Then
Application.ScreenUpdating = False
Sheet8.Unprotect Password:="1"
Dim pt As PivotTable
For Each pt In Sheet8.PivotTables
pt.RefreshTable
Next pt
Sheet8.Protect Password:="1", AllowUsingPivotTables:=True
End If
Next
End Sub
Individual Sheet Codes (inputted into all sheets housing pivots)


Code:
Private Sub Worksheet_Activate()
Refresh
End Sub
My problems:


•The second code shows run time error when a sheet with pivot is opened
•The second code only refreshes when sheets are unprotected
•I need to theoretically bring the two codes together to allow the workbook to open, have all sheets protected (except a cell range in sheet 1 data table, ideally password protected), refresh the sheets that house the pivots when opened, then protect the sheets to avoid mis-edit

Apologies for the extended message but any help would help so much.

Kindest regards,

[HaHoBe]

Hi, luke.walker,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger