VBA code required to refresh multiple queries on different tabs, unprotecting and then reapplying the password

Jmorrison67

New Member
Joined
Aug 20, 2021
Messages
43
Office Version
  1. 2016
Platform
  1. Windows
Good evening MrExcel Community,

I'm looking for a bit of help with VBA (again) :)
I thought it would be quite straight forward what i'm trying to do having managed alrightish with my 1st VBA project the other week....so here goes:

Original VBA for refresh based on one tab, with output in the tab a power query
(tab names and passwords changed)

Sub Refresh_Report()
Sheets("Peter").Unprotect Password:="PETER"
ActiveWorkbook.Connections("Query - PeterDist").Refresh
Sheets("Peter").Protect Password:="PETER"
MsgBox "All data has now refreshed"
End Sub


(Above works fine)

I have 3 more tabs which have their own queries. On each of the tabs I have a ‘refresh’ button which has the above code – switching out the tab and query names. Everything runs fine.

What I have now been asked to do is create a macro which allows the user just to hit ‘refresh’ button once, not 4 times (once per tab, which has a different query on it), so I thought it would just be a case of stacking the code up as follows:

Sub Refresh_ALL()

Sheets("Peter").Unprotect Password:="PETER"

Sheets("James").Unprotect Password:="JAMES"

Sheets("Toby").Unprotect Password:="TOBY"

Sheets("Robert").Unprotect Password:="ROBERT"


ActiveWorkbook.Connection("Query - PeterDist").Refresh

ActiveWorkbook.Connection("Query - JamesDist").Refresh

ActiveWorkbook.Connection("Query - TobyDist").Refresh

ActiveWorkbook.Connection("Query - RobertDist").Refresh


Sheets("Peter").Protect Password:="PETER"

Sheets("James").Protect Password:="JAMES"

Sheets("Toby").Protect Password:="TOBY"

Sheets("Robert").Protect Password:="ROBERT"


MsgBox "All data has now refreshed"


End Sub



When I run this and assign the code to a refresh button 1. The ‘sub bit at the start turns a maroon colour with a dot in the left margin – I have no idea what that mean??

If I try and run the macro in excel the sub turns yellow and nothing happens:

1630616250132.png




Can someone advise if the code is completely wrong?

Ultimate goal is to allow the user to simply click a refresh button which gives them the most up to view / position. All the tabs Peter, James, Toby, Robert are protected with a password and the code should be unlocking each of the tabs, refreshing the data and then reapplying the passwords. I’m sure there is probably an easier way of doing it.

Any help would be much appreciated :)
KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,992
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
OK, this way then.....
VBA Code:
Sub MM1()
Dim MyArr As Variant, ws As Worksheet
Application.ScreenUpdating = False
 MyArr = Sheets(Array("Consol", "Full List of Projects", "Projects with Benefits built in", "Efficiency No Paybacks"))
For Each ws In MyArr
        ws.activate
        ws.Unprotect Password:="PETER"
        If ws.Name = "Consol" Then ActiveWorkbook.Connection("Query - ConsolDist").Refresh
        If ws.Name = "Full List of Projects" Then ActiveWorkbook.Connection("Query - FullListDist").Refresh
        If ws.Name = "Projects with Benefits built in" Then ActiveWorkbook.Connection("Query - BenefitsDist").Refresh
        If ws.Name = "Efficiency No Paybacks" Then ActiveWorkbook.Connection("Query - EfficiencyDist").Refresh
        ws.Protect Password:="PETER"
Next ws
Application.ScreenUpdating = True
End Sub
 
Solution

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Forum statistics

Threads
1,143,835
Messages
5,721,060
Members
422,339
Latest member
SHIVATVM

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