asked on

Protected worksheet Refresh table(not pivot)

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.

ASKER CERTIFIED SOLUTION

Lawrence Salvucci

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Schaefer

ASKER

Thanks that did the trick. Quick question is there a way to standardize this so it can be used in multiple workbooks. Global variable? active workbook instead of hard coding the workbook name?

Lawrence Salvucci

Yes there is. I will post something when I get back to my office. Give me a little bit.

Karen Schaefer

ASKER

ok thanks

Lawrence Salvucci

You can use this code to lock and unlock the sheets. This will just unlock or lock all the sheets. You still need to hard code the password though.

Sub LockSheets()
Dim ws As Worksheet
Dim pwd As String

pwd = "BC1958"  ' Put your password here
For Each ws In Worksheets
    ws.Protect Password:=pwd
Next ws
End Sub

Sub UnlockSheets()
Dim ws As Worksheet
Dim pwd As String

pwd = "BC1958"  ' Put your password here
For Each ws In Worksheets
    ws.Unprotect Password:=pwd
Next ws

End Sub

Open in new window

Then you can use this to refresh your connection to your table

Sub RefreshAllConnections()
Workbooks(ThisWorkbook.Name).RefreshAll
End Sub

Open in new window

Let me know if you need anything else.

Karen Schaefer

ASKER

thanks that's great.