Unprotect worksheet via VBA

M

Mike Echo

I tried to sort a sheet via VBA and kept getting an error until I
figured it was the protection stopping things. How can I turn off
protection, sort, then turn on protection again?

I looked in help and found ActiveWorkbook.Unprotect ("password") but it
doesn't work. Any pointers in the right direction much appreciated.

TIA,
R.
 
C

Chip Pearson

Try

ActiveSheet.Unprotect password:="whatever"
' do your sort
ActiveSheet.Protect password:="whatever"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
M

Mike Echo

ActiveSheet.Unprotect password:="whatever"
' do your sort
ActiveSheet.Protect password:="whatever"
Click to expand...

I found this code (on your website, no less :)) and tried it but I
still get the same error message. If I unprotect the sheet the sort code
is fine. As soon as I protect the sheet things all go pear-shaped. This
has me baffled and I cannot imagine what may be causing it.

I recorded a macro to do the "unprotect then protect" thing and viewed
the code and even though I had entered a password, the macro didn't show
this. It simply showed "ActiveSheet Unprotect" etc.

I'll play more. It is probably something obviously stupid. That's
normally what happens with me. ;-)

Thanks for the reply, Chip.

*** Just had a thought. Could it be because I tend to put re-usable subs
in a module then "Call" them from the worksheet object as opposed to
having the same sub on different worksheets? My file is at work so I'll
have a look tomorrow.
 
Top