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

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
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
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
No...change nothing !!
Ws.name is actually the name of whatever sheet is triggered
The code should loop through each sheet and do whatever is needed if the sheet name matches on of your users names
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
No...change nothing !!
Ws.name is actually the name of whatever sheet is triggered
The code should loop through each sheet and do whatever is needed if the sheet name matches on of your users names
Great - let me give it a go and i'll keep you posted.
Will this code replace the need for the 5 separate macro's i currently have set up? i.e. currently have 1 to unprotect/refresh/protect Peter, 1 to unprotect/refresh/protect James, 1 for Toby and 1 for James PLUS the call one which refreshes them all?

KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
No...hopefully this code will do everybodys refresh !!
Note the comments
Rich (BB code):
Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets 'loops through all worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then 'checks for each users name
        With ws
                .Unprotect Password:=UCase(ws.Name) 'if the user is found, Unprotects the sheet
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh 'uses that persons name and runs the refresh
                .Protect Password:=UCase(ws.Name) 'reprotects the sheet
        End With
    End If
    Next ws 'goes to the next worksheet
MsgBox "All data has now refreshed"
End Sub
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
No...hopefully this code will do everybodys refresh !!
Note the comments
Rich (BB code):
Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets 'loops through all worksheets
    If ws.Name = "James" Or ws.Name = "Robert" Or ws.Name = "Toby" Or ws.Name = "Peter" Then 'checks for each users name
        With ws
                .Unprotect Password:=UCase(ws.Name) 'if the user is found, Unprotects the sheet
                ActiveWorkbook.Connection("Query - " & ws.Name & "Dist").Refresh 'uses that persons name and runs the refresh
                .Protect Password:=UCase(ws.Name) 'reprotects the sheet
        End With
    End If
    Next ws 'goes to the next worksheet
MsgBox "All data has now refreshed"
End Sub
When i run it i get this which relates to the line .Unprotect Password:=UCase(ws.Name)
(I changed Ucase to be my actual password)
1631001367018.png


Line I have in is:
.Unprotect Password:=PETER(ws.Name)

I tried the password in " " but then for the same error above but on the ( this time:

1631002675867.png


KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Did I not say....DO NOT CHANGE ANYTHING IN THE CODE PROVIDED
The variable ws.name is simply a code term for the name of the worksheet, ie, Peter, Toby...whatever
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Did I not say....DO NOT CHANGE ANYTHING IN THE CODE PROVIDED
The variable ws.name is simply a code term for the name of the worksheet, ie, Peter, Toby...whatever
Ah ok, sorry :)
Wasn't sure how it would know what the password was, changed it back to 'UCase' now.

I think i've got a problem though, as the names i've used throughout were just substituted due to confidentiality (but then realised the names etc in the code isnt confidential), so not sure the code will work. I had just thought if i used random names, when i got help with the code i could just switch out the random names for the real ones, but here the 'Refresh' part of the code wont work as the query's are called something different from the tabs (maybe an easy solution though)

For example:

Tab we are referring to as 'PETER'
Tab name = Consol
Query name = ConsolDist
Password = PETER

Tab we are referring to as 'ROBERT'
Tab name = Full List of Projects
Query name = FullListDist
Password = PETER

Tab we are referring to as 'TOBY'
Tab name = Projects with Benefits built in
Query name = BenefitsDist
Password = PETER

Tab we are referring to as 'JAMES'
Tab name = Efficiency No Paybacks
Query name = EfficiencyDist
Password = PETER

So with your code, would i need to repeat the 'activeworkbook' line by just putting in the actual query names? something like this:

Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "Consol" Or ws.Name = "Full List of Projects" Or ws.Name = "Projects with Benefits built in" Or ws.Name = "Efficiency No Paybacks" Then
With ws
.Unprotect Password:=UCase(ws.Name)
ActiveWorkbook.Connection("Query - ConsolDist").Refresh
ActiveWorkbook.Connection("Query - FullListDist").Refresh
ActiveWorkbook.Connection("Query - BenefitsDist").Refresh
ActiveWorkbook.Connection("Query - EfficiencyDist").Refresh
.Protect Password:=UCase(ws.Name)
End With
End If
Next ws
MsgBox "All data has now refreshed"
End Sub


This is the error i get:
1631006358728.png


1631006388566.png


I'm sorry if i'm wasting your time :)

KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Ah ok, sorry :)
Wasn't sure how it would know what the password was, changed it back to 'UCase' now.

I think i've got a problem though, as the names i've used throughout were just substituted due to confidentiality (but then realised the names etc in the code isnt confidential), so not sure the code will work. I had just thought if i used random names, when i got help with the code i could just switch out the random names for the real ones, but here the 'Refresh' part of the code wont work as the query's are called something different from the tabs (maybe an easy solution though)

For example:

Tab we are referring to as 'PETER'
Tab name = Consol
Query name = ConsolDist
Password = PETER

Tab we are referring to as 'ROBERT'
Tab name = Full List of Projects
Query name = FullListDist
Password = PETER

Tab we are referring to as 'TOBY'
Tab name = Projects with Benefits built in
Query name = BenefitsDist
Password = PETER

Tab we are referring to as 'JAMES'
Tab name = Efficiency No Paybacks
Query name = EfficiencyDist
Password = PETER

So with your code, would i need to repeat the 'activeworkbook' line by just putting in the actual query names? something like this:

Sub Refresh_ALL()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name = "Consol" Or ws.Name = "Full List of Projects" Or ws.Name = "Projects with Benefits built in" Or ws.Name = "Efficiency No Paybacks" Then
With ws
.Unprotect Password:=UCase(ws.Name)
ActiveWorkbook.Connection("Query - ConsolDist").Refresh
ActiveWorkbook.Connection("Query - FullListDist").Refresh
ActiveWorkbook.Connection("Query - BenefitsDist").Refresh
ActiveWorkbook.Connection("Query - EfficiencyDist").Refresh
.Protect Password:=UCase(ws.Name)
End With
End If
Next ws
MsgBox "All data has now refreshed"
End Sub


This is the error i get:
View attachment 46426

View attachment 46427

I'm sorry if i'm wasting your time :)

KR Jmorrison67
It really would have helped if you gave us ALL of the information in the first place. ..ill have look tomorrow
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
It really would have helped if you gave us ALL of the information in the first place. ..ill have look tomorrow
Hi Michael,

I know, I apologize - just thought it would be easy enough to switch out made up names for the real ones :)
MrExcel is my new ExcelBible, love the help we get :)

Thanks again

KR Jmorrison67
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I've avoided all of the variables....Try this
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Unprotect Password:="PETER"
Next ws
ActiveWorkbook.RefreshAll
For Each ws In Worksheets
    ws.Protect Password:="PETER"
Next ws
MsgBox "All data has now refreshed"
End Sub
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
I've avoided all of the variables....Try this
VBA Code:
Sub MM1()
Dim ws As Worksheet
For Each ws In Worksheets
    ws.Unprotect Password:="PETER"
Next ws
ActiveWorkbook.RefreshAll
For Each ws In Worksheets
    ws.Protect Password:="PETER"
Next ws
MsgBox "All data has now refreshed"
End Sub
Good morning Michael,

Yes - above code does work but does take quite a while (40 seconds), I guess as it goes through and unprotects/protects every worksheet.

Could there be a way just to do 3 tabs: 'Consol', 'Projects with Benefits built in' and 'Efficiency No Paybacks' as these are only the ones I need the PowerQuery refreshed on? Dunno if it's possible to define the worksheet in the code line?
(I have reworked a solution for 'Full List of Projects' so don't need a query for that one now and so no refresh required there)

Appreciate your help again

KR Jmorrison67
 

Forum statistics

Threads
1,147,635
Messages
5,742,250
Members
423,717
Latest member
rubthenut

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