Macro / VBA

[Hoube78]

Hi,

I have the below macro in my workbook however due to increased number of SQL queries and the fact I need to enter the password for them all it appears the macro locks the sheets before the data is refreshed.

Excel pops up with a message that says the data can not be refreshed as the sheet is protected.

Sub UnprotectRefreshAll()
Dim ws As Worksheet
On Error Resume Next


For Each ws In ActiveWorkbook.Worksheets
ws.Unprotect Password:="MM@123.reg.com"
Next ws




ActiveWorkbook.RefreshAll




For Each ws In ActiveWorkbook.Worksheets
ws.Protect Password:="MM@123.reg.com", _
DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFiltering:=True, AllowUsingPivotTables:=True
Next ws

End Sub

Does any one know away to not run the part in blue until I have entered the password for all the SQL connections / data refreshes?

Thank you in advance.

John

 

[JLGWhiz]

See if the time delay will help.

Sub UnprotectRefreshAll()
 Dim ws As Worksheet, p As Double
 On Error Resume Next
 For Each ws In ActiveWorkbook.Worksheets
 ws.Unprotect Password:="MM@123.reg.com"
 Next ws
 ActiveWorkbook.RefreshAll
p = Timer + 2
Do While Timer < p
	DoEvents
Loop
For Each ws In ActiveWorkbook.Worksheets
 ws.Protect Password:="MM@123.reg.com", _
 DrawingObjects:=True, Contents:=True, Scenarios:=True _
 , AllowFiltering:=True, AllowUsingPivotTables:=True
 Next ws
 End Sub

You can increase or decrease the time span by changing the value of the 2. Whole numers are in seconds, but you can use .1 to .9 or .01 to .99 for tenths and hundreths of a second if needed.