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

[Jmorrison67]

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:

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]

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

 

[Jmorrison67]

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]

No...hopefully this code will do everybodys refresh !!
Note the comments

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]

No...hopefully this code will do everybodys refresh !!
Note the comments

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)

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:

KR Jmorrison67

 

[Michael M]

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]

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:

I'm sorry if i'm wasting your time

KR Jmorrison67

 

[Michael M]

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]

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]

I've avoided all of the variables....Try this

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]

I've avoided all of the variables....Try this

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