Excel macro to unlock a password protected tab (known password)

[HangerAngler]

Does anybody know how an Excel VBA macro can be written so it unlocks a tab that is password protected? The password is known.

[Abused Student]

Sub UnprotectCurrent()
ActiveWorkbook.ActiveSheet.Unprotect ("password")


End Sub

[dumples]

What AS said, though if you know the name of the worksheet you should use it directly.

Thisworkbook.worksheets("Sheet1").Unprotect ("password")

One question that I would ask is why do you want to unlock it? If you want to unlock it so that you can perform some operations on it with code and then re-lock it, then you can save yourself a hastle by just locking it with VB and setting the userInterfaceOnly command to true.

This code will run without an error:

Sub tle()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Protect userInterfaceOnly:=True
ws.Cells(1, 1) = ws.Cells(1, 2)
End Sub

While this code will bomb

 
Sub par()
Dim ws As Worksheet
Set ws = ActiveSheet
ws.Protect userInterfaceOnly:=False
ws.Cells(1, 1) = ws.Cells(1, 2)
End Sub

[campbell]

One question that I would ask is why would you want it password-locked at all? [I find password-locking to be obnoxious; and much of it is easily crackable anyway. It's more of an annoyance to the person who comes later trying to fix the spreadsheet problems.]

If you're just unlocking it, and that's it, then yeah, the VBA given is fine.

[spencerhs5]

Originally Posted by campbell

One question that I would ask is why would you want it password-locked at all? [I find password-locking to be obnoxious; and much of it is easily crackable anyway. It's more of an annoyance to the person who comes later trying to fix the spreadsheet problems.]

If you're just unlocking it, and that's it, then yeah, the VBA given is fine.

What do you do when you have to give the file to somoene who you anticpate might have very little computer skills but think that it would be a good idea to try and change/personalize a sheet.

[Abused Student]

Originally Posted by campbell

One question that I would ask is why would you want it password-locked at all? [I find password-locking to be obnoxious; and much of it is easily crackable anyway. It's more of an annoyance to the person who comes later trying to fix the spreadsheet problems.]

If you're just unlocking it, and that's it, then yeah, the VBA given is fine.

I have spreadsheets locked for people down the line to use that I do not want them messing with the formulas. Reserves worksheets and stuff where they only get certain inputs. I have a button to unlock a sheet and another to relock it.

[campbell]

There are two possibilities here:

1. Person you're giving the spreadsheet knows what they're doing. In which case, they can unlock it, password or no. There are Excel password crackers all around the place. You're just making them waste their time.

2. Person doesn't know what they're doing. Locking sans password generally will deter these folks. [I use this all the time... the point is to prevent accidental changes. Deliberate changes, I'm not going to try to stop.]

If you need something super-secure, you shouldn't be doing it in a spreadsheet. There are no real controls.

[Abused Student]

Originally Posted by campbell

There are two possibilities here:

1. Person you're giving the spreadsheet knows what they're doing. In which case, they can unlock it, password or no. There are Excel password crackers all around the place. You're just making them waste their time.

2. Person doesn't know what they're doing. Locking sans password generally will deter these folks. [I use this all the time... the point is to prevent accidental changes. Deliberate changes, I'm not going to try to stop.]

If you need something super-secure, you shouldn't be doing it in a spreadsheet. There are no real controls.

I am dealing with #2 for the most part. Actually some are more in between the two, know just enough to cause all kinds of problems.

[ADoubleDot]

Here is a macro for doing it when you don't know the password. Seriously, excel passwords are stupid.

[campbell]

The biggest problem is being the person who has to maintain the spreadsheet after the originator left.

Seriously, locking without password generally will keep people messing with formulas accidentally. If they don't have the discipline not to mess with stuff you tell them not to mess with, sounds like you've got other issues going on. Because they will mess with something, password-locking or no.