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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

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,136,423
Messages
5,675,765
Members
419,585
Latest member
popsin

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