VBA protect workbook

[vbaNewby]

Hi,

I am starting with a protected workbook. I then run a macro. I know you can at the start of the macro, unprotect it and then at the end protect it but this leaves room for an unprotected workbook if the macro crashes.

ActiveWorkbook.Unprotect Password:="xxx"
ActiveWorkbook.protect Password:="xxx"

So to get rid of that scenario I want to only allow the macro to work with the workbook.

I've tried:

ActiveWorkbook.Protect userinterfaceonly:=True

I get an error. Plus this doesn't make sense, since the workbook is protected from the get-go (before the macro makes this call)

Any solutions without un-protecting and then re-protecting?

Thanks in advance. Kind regards.

 

[VoG]

Hi. Perhaps like this

On Error GoTo ErrHandl
ActiveWorkbook.Unprotect Password:="xxx"
'
'your code here
'
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"

 

[MrKowz]

A possibility would be to put an error handler in there:

Public Sub YourMacro()
On Error Goto CleanUp
'your code
CleanUp:
   ActiveWorkbook.Protect Password:="xxx"
   End Sub

 

[vbaNewby]

Hi. Perhaps like this

On Error GoTo ErrHandl
ActiveWorkbook.Unprotect Password:="xxx"
'
'your code here
'
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"

Hi vog! This seems like a possible solution. Only thing is if I have multiple subs and I wrap my code around this in my main sub, don't I have to do this for every small sub that my main sub calls? (just in case my other subs have an error)

! hope that makes sense.

 

[VoG]

Yes, you would need in each sub

On Error GoTo ErrHandl

'your code here
'
Exit Sub
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"

 

[vbaNewby]

A possibility would be to put an error handler in there:

Public Sub YourMacro()
On Error Goto CleanUp
'your code
CleanUp:
   ActiveWorkbook.Protect Password:="xxx"
   End Sub

Thanks MrKowz, please seem my previous post for my concerns with this.

 

[vbaNewby]

Yes, you would need in each sub

On Error GoTo ErrHandl

'your code here
'
Exit Sub
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"

Thanks Vog, seems a little cumbersome but I guess this is the best possible solution.

Kind regards.

 

[MrKowz]

Yes, you would need in each sub

On Error GoTo ErrHandl
 
'your code here
'
Exit Sub
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"

VoG, would this also prevent the user from bypassing the error handler by breaking the code manually with Esc?

 

[VoG]

VoG, would this also prevent the user from bypassing the error handler by breaking the code manually with Esc?

Hi there. I don't think so. You would need

Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrHandl
 
'your code here
'
Exit Sub
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"
Application.EnableCancelKey = xlInterrupt

 

[vbaNewby]

Hi there. I don't think so. You would need

Application.EnableCancelKey = xlErrorHandler
On Error GoTo ErrHandl
 
'your code here
'
Exit Sub
ErrHandl:
ActiveWorkbook.Protect Password:="xxx"
Application.EnableCancelKey = xlInterrupt

MrKowz. Great point. Vog thanks for the additional tip.