I have an Excel 2010 workbook. One worksheet imports data from an external data connection (SQL query). I have also added additional columns to the worksheet to perform calculations on the data and to massage it a bit. The worksheet forms the backbone of the raw data used in the other worksheets.
I'd like to protect the worksheet to make it read-only (allowing sort, filter, pivot table usage). I know how to do this with the protect worksheet feature. But when the worksheet is protected, I can't use the Refresh button to refresh the data from the source and I want users to be able to do this. I was going to configure the connection properties to automatically refresh on open and allow manual refreshes.
Has anyone found an elegant way of enabling the protect worksheet functionality and enabling an external data refresh, without allowing users to change cell values themselves?
When you want to refresh an Excel pivot table on a protected sheet, you will need to temporarily unprotect the worksheet, refresh the pivot table, and then protect the sheet again. You can do those steps manually, or use a macro, like the one below. Replace the “mypassword” string with the password for your worksheet.
When you refresh a workbook in view mode, you simply update the data displayed in the workbook. You can do this in a browser window or in Excel, in read-only (not edit) mode. In this case, the workbook is not tracked as a changed file.
Click Review > Restrict Editing. Under Editing restrictions, check Allow only this type of editing in the document, and make sure the list says No changes (Read only). Click Yes, Start Enforcing Protection.
1. Select the cells you need to protect their formatting but only allow data entry, then press Ctrl + 1 keys simultaneously to open the Format Cells dialog box. 2. In the Format Cells dialog box, uncheck the Locked box under the Protection tab, and then click the OK button.
Based on Pankaj's suggestion I did the following (although I don't think it's very elegant and still think there must be a better way).
I created a new macro for the workbook.
Sub RefreshData()
'
' RefreshData Macro
'
Application.ScreenUpdating = False
Sheets("sheetname").Unprotect Password:="password"
ActiveWorkbook.Connections("connection name").Refresh
Sheets("sheetname").Protect _
Password:="password", _
UserInterfaceOnly:=True, _
AllowFiltering:=True, _
AllowSorting:=True, _
AllowUsingPivotTables:=True
End Sub
Then I opened up ThisWorkbook in the VBA Project and edited the Workbook Open routine.
Private Sub Workbook_Open()
RefreshData
End Sub
More info about the protection options can be found here: http://datapigtechnologies.com/blog/index.php/worksheet-protection-best-practice/
It works; the sheet is locked everytime the workbook is opened and a refresh of the data is performed. The UserInterfaceOnly property doesn't make a difference to the command to refresh the data (although it should to other macro events). You will still have to specifically unlock the spreadsheet, perform the data refresh and then lock the sheet again.
I added a form button onto one of the other sheets and linked it to my RefreshData macro so that the data can be refreshed manually, while the sheet is supposedly locked.
The other thing I did in the Connection Properties, was to remove the tick against the background refresh.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With