Use VBA to refesh a query on a protected workbook

rgrovier

New Member
Joined
Sep 13, 2006
Messages
15
I have an Excel file that acts as an input form for a mailmerge operation. The Excel file contains an Auto-run VBA program designed to clean-up entries made by the previous user of the file. The worksheet is protected to prevent tampering in all cells except those designated for user input. However, the protected cells are the result of a query that I would like to refresh when the Auto-run VBA program is run upon opening the file.

I can get the query to refresh if I remove the protection but I can't get the VBA program to remove the protection, update the query and then restore the protection.

Any thoughts would be greatly appreciated.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Have you tried:

Code:
ActiveSheet.Unprotect "PasswordHere"
   '  Query refresh code
ActiveSheet.Protect "PasswordHere"

You can do the same for WB protection, and/or chage "ActiveSheet" to the actual sheet name.

HTH,

Smitty
 

rgrovier

New Member
Joined
Sep 13, 2006
Messages
15
Smitty,

I think I've tried every possible method using "refresh" and every possible position of the command in the script. The rest of the code seems to work fine ... except the querytable refresh. I'm sure I'm missing something basic but I can't for the life of me figure it out. Here is what I have ...

Code:
Sub Auto_Open()
'
' Auto_Open Macro
'
'
Application.ScreenUpdating = False
Worksheets("Build-A-Label").Unprotect ("") 'currently the password is blank
    Worksheets("Build-A-Label").Range("b5").QueryTable.Refresh
Sheets("Label List").Select
    Worksheets("Label List").Range("A2:iv65536").ClearContents
    Selection.ClearContents
    Worksheets("Label List").Range("A2").Select
Sheets("Build-A-Label").Select
    Range("A5:A65536").Select
    Selection.ClearContents
    Worksheets("Build-A-Label").EnableCalculation = True
    Worksheets("Build-A-Label").EnableCalculation = False
With Worksheets("Build-A-Label")
    EnableSelection = xlUnlockedCells
    Protect AllowFiltering:=True
End With
Application.ScreenUpdating = True
Range("A5").Select
End Sub

Thanks!
Reich
 

Forum statistics

Threads
1,170,943
Messages
5,872,867
Members
432,950
Latest member
ALeXceLBr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top