Protecting sheet with update vba script
 

You can pass UserInterfaceOnly=True with Protect method to allow macros:

Sub ProtectUIOnly()

Sheet1.Protect Password:="password", _
UserInterfaceOnly:=True

Sheet1.Cells(1, 1) = "test"

End Sub

--
urkec


"Clayton Dool" wrote:

I have a workbook with multiple sheets. When trying to protect the
sheets, I can no longer update the data and underlying pivot tables
programmatically. How do I approach this?




Workbook
----------------------------------
- Sheet 1
- Contains Query Table linked to SQL
- (HIDDEN)
- Contains dummy cell that has formula that causes
Worksheet_Calculation to be run when query
is refreshed.
- Sheet 2, 3, and 4
- Contain Pivot Tables referencing Query Table Above
- Contain button that runs code that refreshes data.



Code/Macro
-------------------------------
Sub RefreshData()
Dim iP As Integer
Dim iW As Integer

For iW = 1 To Worksheets.Count
If (Worksheets(iW).Name = "Data") Then
Worksheets(iW).QueryTables("DataQuery").Refresh
BackgroundQuery:=False
End If
Next
End Sub



Private Sub Worksheet_Calculate()
Dim iP As Integer
Dim iW As Integer

Application.DisplayAlerts = False
Application.ScreenUpdating = False

For iW = 1 To Worksheets.Count
For iP = 1 To Worksheets(iW).PivotTables.Count
Worksheets(iW).PivotTables(iP).RefreshTable
Next
Next

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub




Thanks,
Clayton