Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protecting formulas without protecting sheet so grouping still wor | Excel Discussion (Misc queries) | |||
update excel files using a vba script | Excel Programming | |||
Update a workbook from a script | Excel Discussion (Misc queries) | |||
update Link from protected wb OR prompt for protecting a worksheet | Excel Programming | |||
Script or Tool to Update Excel Links | Excel Programming |