Unprotect then protect after Workbook is opened

[Learner123]

Hi,

I am having a hard time getting the below code to work.

[vba]
Private Sub Workbook_Open()
On Error Resume Next
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll
ActiveSheet.Protect
End Sub

[/vba]

When the workbook is last closed it is saved as a protected file. I need for it to open as an unprotected sheet so that it can import data. The code works well if the the below code line is removed:

[vba] ActiveSheet.Protect[/vba]

However, removing the above code leaves the worksheet unprotected. Any idea how to automatically protect the sheet after it refreshes?

Thanks in advance!

[Kenneth Hobs]

[vba]Private Sub Workbook_Open()
Sheet1.Protect "ken", UserInterfaceOnly:=True
ThisWorkbook.RefreshAll
End Sub[/vba]

[Learner123]

Thanks Ken for your response, but I was hoping to unprotect the worksheet upon workbook startup, then refresh, the protect. Anyway to do this?

Thanks again!!

[Kenneth Hobs]

Try my method and then tell me if it does not meet your need.

[sukumar.vb]

Make sure that you activate sheet which you want to protect before closing workbook.

[vba]

Private Sub
Workbook_Open()
On Error Resume Next
ActiveSheet.Unprotect
ActiveWorkbook.RefreshAll

End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveSheet.Protect
End Sub

[/vba]

[Learner123]

Hi,

I am following your directions, but it does not seem to solve my problem.

I have altered the code and was wondering if there were anyone else who may be able to provide some guidance.

[VBA]Private Sub Workbook_Open()
On Error Resume Next
Sheets("Sheet1").Select
Sheets("Sheet1").Unprotect
ActiveWorkbook.RefreshAll
Call Protect1
End Sub[/VBA]

Again, the issue appears when going from "RefreshAll" to "Call Protect1".

Thank you very much in advance!

[Learner123]

For those who were wondering, I found the answer on another site:

"
I would strongly suggest against running it twice or adding a time delay as you never know if the time delay will be long enough.

I had this problem too and what you need to do is change the setting of your data connections. By default, Excel will "Enable background refresh". This must be turned off and it will force the macro to complete the refresh task before it moves on. If using Excel 2007, bring up the "Connection Properties" and uncheck the box that enables the background refresh. You can get there by clicking on the table that gets refreshed and where you would click to actually refresh the data you should see an arrow for more opions.

Your solutions only worked because they by chance, gave excel enough time to refresh before moving on. Doing what I have suggested is a far more definitive approach and will reduce bugs moving forward with your coding. I hope this helps, from one programmer to another. "