Unprotect - UpdateAll - Protect Jumps to another sheet

[Ipap1]

Hi All,
First time poster here and very new to VBA script.. learning as I go as of a couple days ago.

Before I get into the problem, here is some context. (Hopefully it makes sense)
I have been working on an excel document that pulls data from an online source.
There are 4 sheets on this document. Sheets1 and 2 and like a dashboard that presents the findings from the data.
Sheets 3 and 4 show the summarised background data for people who would like to drill into the details.
The background data sheets (3 & 4) are the product of me using power queries to filter out and amend the info from online data source.

Now the task:
I have created refresh buttons on Sheets 1 & 2 that are meant to refresh the query data.
In amongst that, I would also like Sheets 3 & 4 protected so the data cannot be altered before it gets displayed onto sheets 1 & 2.
(The online data source also requires a login so whoever doesn't have an account cannot refresh the data or edit the queries. This is by done design)

The Script:

Sub DataRefresh()


Sheet3.Unprotect ("123")
Sheet4.Unprotect ("123")


ActiveWorkbook.RefreshAll


Sheet1.Range("A11").Value = Now()
Sheet1.Range("A13").Value = Application.UserName
Sheet1.Columns("A").AutoFit
Sheet2.Range("A11").Value = Now()
Sheet2.Range("A13").Value = Application.UserName
Sheet2.Columns("A").AutoFit


Sheet3.Protect ("123")
Sheet4.Protect ("123")


End Sub

The issue:
Now it runs well. Does everything that is required, but when I click on one of the refresh buttons I created, lets say on Sheet 1, the macro makes Sheet 3 active, every time.
I want it to refresh without activating another sheet.
Some more info:
I have disabled background refresh on the queries and the data refresh also takes a bit of time. I tried adding in a delay from Unprotect to RefreshAll and that doesn't fix the problem. Also tried to do it on a clean version of the file, so no other accidental macros in the background...

Thanks in advance for your help and really excited to be learning a new skill.

 

[DanteAmor]

Hello @Ipap1

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

I didn't find the cause why sheet3 is selected.
Protecting the sheet changes the sheet, but I don't know why it does this.

But try the following to return to the source sheet:

Sub DataRefresh()
  Dim act As Worksheet
  Set act = ActiveSheet
 
  Sheet3.Unprotect ("123")
  Sheet4.Unprotect ("123")
 
  ActiveWorkbook.RefreshAll
 
  Sheet1.Range("A11").Value = Now()
  Sheet1.Range("A13").Value = Application.UserName
  Sheet1.Columns("A").AutoFit
  Sheet2.Range("A11").Value = Now()
  Sheet2.Range("A13").Value = Application.UserName
  Sheet2.Columns("A").AutoFit
 
  Sheet3.Protect ("123")
  Sheet4.Protect ("123")
 
  act.Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[Ipap1]

Hello @Ipap1

Welcome to the MrExcel forum. Please accept my warmest greetings and sincere hope that all is well.​

I didn't find the cause why sheet3 is selected.
Protecting the sheet changes the sheet, but I don't know why it does this.

But try the following to return to the source sheet:

Sub DataRefresh()
  Dim act As Worksheet
  Set act = ActiveSheet
 
  Sheet3.Unprotect ("123")
  Sheet4.Unprotect ("123")
 
  ActiveWorkbook.RefreshAll
 
  Sheet1.Range("A11").Value = Now()
  Sheet1.Range("A13").Value = Application.UserName
  Sheet1.Columns("A").AutoFit
  Sheet2.Range("A11").Value = Now()
  Sheet2.Range("A13").Value = Application.UserName
  Sheet2.Columns("A").AutoFit
 
  Sheet3.Protect ("123")
  Sheet4.Protect ("123")
 
  act.Select
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

Hello @DanteAmor,
Thank you for the warm welcome. I hope all is well with you also.

Thank you also for your reply. I can confirm that your recommendation has indeed solved my problem.
The macro switches to sheet 3 and then back to the sheet that was originally active.
Thank you very much for your help!

Warmest Regards,
Ioannis

 

[Michael M]

In that case turn off screenUpdating at the start of the code and back on at the end.

Sub DataRefresh()
  Dim act As Worksheet
  application.screenupdating=false
  Set act = ActiveSheet
 
  Sheet3.Unprotect ("123")
  Sheet4.Unprotect ("123")
 
  ActiveWorkbook.RefreshAll
 
  Sheet1.Range("A11").Value = Now()
  Sheet1.Range("A13").Value = Application.UserName
  Sheet1.Columns("A").AutoFit
  Sheet2.Range("A11").Value = Now()
  Sheet2.Range("A13").Value = Application.UserName
  Sheet2.Columns("A").AutoFit
 
  Sheet3.Protect ("123")
  Sheet4.Protect ("123")
 
  act.Select
application.screenupdating=true
End Sub

 

[Ipap1]

In that case turn off screenUpdating at the start of the code and back on at the end.

Sub DataRefresh()
  Dim act As Worksheet
  application.screenupdating=false
  Set act = ActiveSheet
 
  Sheet3.Unprotect ("123")
  Sheet4.Unprotect ("123")
 
  ActiveWorkbook.RefreshAll
 
  Sheet1.Range("A11").Value = Now()
  Sheet1.Range("A13").Value = Application.UserName
  Sheet1.Columns("A").AutoFit
  Sheet2.Range("A11").Value = Now()
  Sheet2.Range("A13").Value = Application.UserName
  Sheet2.Columns("A").AutoFit
 
  Sheet3.Protect ("123")
  Sheet4.Protect ("123")
 
  act.Select
application.screenupdating=true
End Sub

Hi @Michael M. Thank you for the suggestion!
This is now working perfectly.
I had tried application.screenupdating = True/false originally and that hadn't worked.
But now with the help of Dante's added lines the screen updating command works perfectly.
Thank you all for the help!

 

[Michael M]

Glad WE could assist.