Protected worksheet Refresh table(not pivot)

Question

I need to protect my worksheet, but still be able to refresh the underlying data(table).

My worksheet is an Invoice with the body of the invoice is a table linked to underlying query. How do I refresh this data when I have locked down (protected worksheet) w/password? The only code I can find seems to unlock & Lock by storing the password within the VBA. There has to be another method.

SierraOscar SierraOscar · Accepted Answer · 2016-07-26T22:08:03

Lock your sheet like so in the workbook open event:

Private Sub Workbook_Open()

    ThisWorkbook.Sheets("Invoice").Protect Password:="Password123", UserInterfaceOnly:= True

End Sub

The UserInterfaceOnly argument allows VBA to make changes to the worksheet but not the user - unforunately this option can only be used programatically on not via GUI

Protected worksheet Refresh table(not pivot)

1 Answers