Confused with VBA - Macro works fine in one workbook, but when I copy same code into another workbook, it doesn't work

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Good morning All,

I'm looking for some help with what is probably a simple bit of VBA coding. I am not familiar with VBA and got the code from online biccountant i think it was.

What the code is intended to do is:
Unprotect a worksheet named "Consol" >>> Consol is the output of a power query
Refresh the query
Protect the worksheet again

It works fine one the 1st workbook i tried it in but when i use the name code, but changing the query name, it doesnt work....tried googling for 3+ hours to find a solution but REALLY struggling.

Code that works in workbook 1:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolPSC").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub


Code that doesnt work in workbook 2:

Sub Refresh_Report()

Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub




Only thing i changed was the query name from ConsolPSC to ConsolDist as the ConsolPSC is the name of the query in workbook 1 and Consol Dist the name in workbook 2. My thinking was that i would need to define the 'Activeworkbook', maybe rename it to whatever the workbook is called - i'm sure it is something really simple.

The error i get in the 2nd workbook is:
which is ofcourse the standard message you get when you try to enter into a protected worksheet.
1629452832955.png



Any help would be appreciated

Kind regards
Jmorrison67
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
In workbook 2, is there actually a sheet called "Consol" (case sensitive).
Also, is it actually protected?
If so, is the password actually "PASS" (also case sensitive)?
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
In workbook 2, is there actually a sheet called "Consol" (case sensitive).
Also, is it actually protected?
If so, is the password actually "PASS" (also case sensitive)?
Hi Sykes,
Thanks for your reply.

1. Yes - see below:
1629457287601.png


2. Yes - protected - see below:
This is the refresh icon i put in, which i assigned the macro to
1629457469271.png


when i click the icon - this happens:

Message i would expect to see if it's actually updated

1629457539863.png


Followed by this:

1629457561860.png





I'm just confused as it is exactly the same code as in my other workbook - the other workbook is for a different part of the business, but has the same layout, tabs etc its just called something different



3. Yes - password is all caps PASS
 

Attachments

  • 1629457525775.png
    1629457525775.png
    778 bytes · Views: 4

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
May I suggest unprotecting the sheet manually, then running your code again, but without the protection lines, thus:
(I've commented-out the lines, so they won'[t run).
VBA Code:
Sub Refresh_Report()

'Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

'Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

May I suggest unprotecting the sheet manually, then running your code again, but without the protection lines, thus:
(I've commented-out the lines, so they won'[t run).
VBA Code:
Sub Refresh_Report()

'Sheets("Consol").Unprotect Password:="PASS"

ActiveWorkbook.Connections("Query - ConsolDist").Refresh

'Sheets("Consol").Protect Password:="PASS"

MsgBox "All data has now refreshed"

End Sub
Hi Sykes,

I removed the unprotect / protect bits and ran the code and worked fine i.e. refreshed the query as would expect to see.

But the ultimate goal is to get the code to unprotect the sheet, refresh it and then protect it (the scenario is to make a file user friendly for non IT / technophobes and so they dont change any of the data on the 'Consol' tab in the workbook. Is there any alternative to these lines:

'Sheets("Consol").Protect Password:="PASS"
'Sheets("Consol").Unprotect Password:="PASS"

It is strange (to me) how it works fine in one workbook but not another 0- would there be a conflict somewhere?
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
... so it's purely a matter of getting the sheet unprotected, programmatically.

With the sheet manually protected (also try to manually change a cell's content, to confirm protection is on), run just the one line of code:
VBA Code:
Sheets("Consol").Unprotect Password:="PASS"
...then manually try to change a cell's contents, again.
Does the protection come off the sheet?

Also, where is this code residing? Is it in the 2nd workbook's VBA project?
Sheet module, or a standalone module?
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

... so it's purely a matter of getting the sheet unprotected, programmatically.

With the sheet manually protected (also try to manually change a cell's content, to confirm protection is on), run just the one line of code:
VBA Code:
Sheets("Consol").Unprotect Password:="PASS"
...then manually try to change a cell's contents, again.
Does the protection come off the sheet?

Also, where is this code residing? Is it in the 2nd workbook's VBA project?
Sheet module, or a standalone module?
OK
So i run the code just as above :
Sheets("Consol").Unprotect Password:="PASS"
And it unprotected the sheet
and yes i can manually change the contents of the cells (protection comes off)

FYI - this is literally my 1st ever attend at VBA, as i was asked by my manager to come up with this button thing for the non IT guys to just hit to refresh the data on that Consol tab

The below is where i have it running
steps i found online were:
Alt + F11 to open the VBA window
Insert > Module
Copy the code as above
Press save

Go back to excel
assign a macro to the refresh icon
run

(I have blocked out confidential info below)

1629464608323.png
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
I've just had a thought...
If your query's set to "Background refresh" (one of its properties) then the rest of your code will be allowed to run, even though the query hasn't necessarily completed.
If this was the case, the protection will come off your sheet, the refresh will start, the message Box will display, and the sheet will be re-protected, all possibly before the refresh has finished (or even started) updating the data in your table.
In DATA, go to "Queries & connections", find your query, on the RHS, right-click, then you should get this:

Screenshot 2021-08-20 141732.jpg

...ensure that "Enable Background Refresh" is unchecked, and try the full routine, again.
 
Solution

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,883
Office Version
  1. 365
Platform
  1. Windows
... well done on your first VBA efforts by the way - bit daunting when you first start!

also - Welcome to Mr. Excel!
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Sykes!!!
YOU
ARE
AMAZING!

This is what i had:
1629466343106.png




Unchecked it, re-entered the full code and hey presto!!
Phew - i am so relieved now - you've topped off my friday / week :)


Thanks you so much
from a very grateful accountant :)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,237
Messages
5,768,967
Members
425,507
Latest member
AndreaWorkPlace

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