Today one of my co-workers in Finance department asked me to take a look at an Excel worksheet. He needed to add something in but all modification options like Insert, Delete were grayed out. He mentioned he was not the original guy did this worksheet and the guy who did this worksheet had left.
As you can tell, I have no idea about Excel protection at that moment. But anyway, I used Google again. Surprise, the first search result resolved this problem.
According to the website http://mcgimpsey.com/excel/removepwords…., the only thing you need to do is to run a VBA Macro which can reserve out the “password”. The Macro code is attached as the following.
Of course, you can download the workbook allinternalpasswords.xls if you don’t want to cut and paste the macro below. The workbook is hidden, and has an attached toolbar with a button to start the macro. Activate the workbook you want to unlock and click the button. The code is unlocked so you may examine and modify it as needed.
Remember: if you are going to open the allinternalpasswords.xls in your Excel application, you will have to downgrade your Security level first.
Public Sub AllInternalPasswords()
' Breaks worksheet and workbook structure passwords. Bob McCormick
' probably originator of base code algorithm modified for coverage
' of workbook structure / windows passwords and for multiple passwords
'
' Norman Harker and JE McGimpsey 27-Dec-2002 (Version 1.1)
' Modified 2003-Apr-04 by JEM: All msgs to constants, and
' eliminate one Exit Sub (Version 1.1.1)
' Reveals hashed passwords NOT original passwords
Const DBLSPACE As String = vbNewLine & vbNewLine
Const AUTHORS As String = DBLSPACE & vbNewLine & _
"Adapted from Bob McCormick base code by" & _
"Norman Harker and JE McGimpsey"
Const HEADER As String = "AllInternalPasswords User Message"
Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"
Const REPBACK As String = DBLSPACE & "Please report failure " & _
"to the microsoft.public.excel.programming newsgroup."
Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _
"now be free of all password protection, so make sure you:" & _
DBLSPACE & "SAVE IT NOW!" & DBLSPACE & "and also" & _
DBLSPACE & "BACKUP!, BACKUP!!, BACKUP!!!" & _
DBLSPACE & "Also, remember that the password was " & _
"put there for a reason. Don't stuff up crucial formulas " & _
"or data." & DBLSPACE & "Access and use of some data " & _
"may be an offense. If in doubt, don't."
Const MSGNOPWORDS1 As String = "There were no passwords on " & _
"sheets, or workbook structure or windows." & AUTHORS & VERSION
Const MSGNOPWORDS2 As String = "There was no protection to " & _
"workbook structure or windows." & DBLSPACE & _
"Proceeding to unprotect sheets." & AUTHORS & VERSION
Const MSGTAKETIME As String = "After pressing OK button this " & _
"will take some time." & DBLSPACE & "Amount of time " & _
"depends on how many different passwords, the " & _
"passwords, and your computer's specification." & DBLSPACE & _
"Just be patient! Make me a coffee!" & AUTHORS & VERSION
Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _
"Structure or Windows Password set." & DBLSPACE & _
"The password found was: " & DBLSPACE & "$$" & DBLSPACE & _
"Note it down for potential future use in other workbooks by " & _
"the same person who set this password." & DBLSPACE & _
"Now to check and clear other passwords." & AUTHORS & VERSION
Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _
"password set." & DBLSPACE & "The password found was: " & _
DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _
"future use in other workbooks by same person who " & _
"set this password." & DBLSPACE & "Now to check and clear " & _
"other passwords." & AUTHORS & VERSION
Const MSGONLYONE As String = "Only structure / windows " & _
"protected with the password that was just found." & _
ALLCLEAR & AUTHORS & VERSION & REPBACK
Dim w1 As Worksheet, w2 As Worksheet
Dim i As Integer, j As Integer, k As Integer, l As Integer
Dim m As Integer, n As Integer, i1 As Integer, i2 As Integer
Dim i3 As Integer, i4 As Integer, i5 As Integer, i6 As Integer
Dim PWord1 As String
Dim ShTag As Boolean, WinTag As Boolean Application.ScreenUpdating = False
With ActiveWorkbook
WinTag = .ProtectStructure Or .ProtectWindows
End With
ShTag = False
For Each w1 In Worksheets
ShTag = ShTag Or w1.ProtectContents
Next w1
If Not ShTag And Not WinTag Then
MsgBox MSGNOPWORDS1, vbInformation, HEADER
Exit Sub
End If
MsgBox MSGTAKETIME, vbInformation, HEADER
If Not WinTag Then
MsgBox MSGNOPWORDS2, vbInformation, HEADER
Else
On Error Resume Next
Do 'dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
With ActiveWorkbook
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If .ProtectStructure = False And _
.ProtectWindows = False Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND1, _
"$$", PWord1), vbInformation, HEADER
Exit Do 'Bypass all for...nexts
End If
End With
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
If WinTag And Not ShTag Then
MsgBox MSGONLYONE, vbInformation, HEADER
Exit Sub
End If
On Error Resume Next
For Each w1 In Worksheets
'Attempt clearance with PWord1
w1.Unprotect PWord1
Next w1
On Error GoTo 0
ShTag = False
For Each w1 In Worksheets
'Checks for all clear ShTag triggered to 1 if not.
ShTag = ShTag Or w1.ProtectContents
Next w1
If ShTag Then
For Each w1 In Worksheets
With w1
If .ProtectContents Then
On Error Resume Next
Do 'Dummy do loop
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If Not .ProtectContents Then
PWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _
Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
MsgBox Application.Substitute(MSGPWORDFOUND2, _
"$$", PWord1), vbInformation, HEADER
'leverage finding Pword by trying on other sheets
For Each w2 In Worksheets
w2.Unprotect PWord1
Next w2
Exit Do 'Bypass all for...nexts
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
Loop Until True
On Error GoTo 0
End If
End With
Next w1
End If
MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER
End Sub
i have used the allinternalpssword.xls. it added a addon to my excel programme. it has a pop up which gives options for remove password.i want to remove the addon from my excel programme. can i do that . or i need to reinstall the programme? thanks. have a nice day.
[Reply]
Thanks for this useful macro, it solved the problem, and also thanks to the original author.
[Reply]
Thanks so much. You saved me hours of retyping. Loved the comment about making you a coffee while waiting for the password …. if I could have, I would have. Thanks again.
[Reply]
Thank you very much to the author, it helped me a lot..
[Reply]
superb, amazing, it works me lot
thnx
[Reply]
Super! One of the best tools available and very user friend. \
Thanks a lot for the Author!!!!
[Reply]
Could you please tell me how to run that code. i mean that code which is here how to “run” it.
[Reply]
Worked a treat. Many thanks. Great work
[Reply]
THANK YOU!!! Saved me a TON of time!!! Couldn’t imagine life now without the internet and helpful people who don’t mind sharing thier “tips” for FREE! :0)
[Reply]
I have run the program and get error 91 on runing the macro.
pasting the code in I get compile syntax error on this line
“Const AUTHORS As String = DBLSPACE & vbNewLine & _”
I am using excel 2003 with SP3
[Reply]
Sweet! Worked like a charm, thanks so much!!!
[Reply]
Excellent macro!!! works smoothly…..
Thanks a ton!
[Reply]
I was nervous about downloading a macro from a random (to me, at least) website, but it worked great. THANK YOU!
[Reply]
Hi, the macro works great and the sheet gets unprotected. But even if I save an unprotected version of it, when I open it is protected again with the same password… This means I have to run the macro every time I want to use the file.
Please some help!
[Reply]
Thank you, maybe I didn’t explain myself well. I mean, is there a way to fully remove the password, and not having to re-enter it each time I want to open the file?
Thanks for your help.
[Reply]
First day on my new job and was asked by the CEO to unprotect a 15pg workbook with 2 yrs of financial data. The tool worked great and I’m off to a great start. Trust the code!!!
[Reply]
thanks.. its worked!!
[Reply]
thanks! this was really helpful!
[Reply]
Thnx a trillion man!!!!!!!!
u saved my tons of time………
[Reply]
I am running 2007 version, and the workbook is protected by password and when I click the add in’s button and click remove password the message comes up saying the work book isn’t protected by a password? But it is! Any Help pls.
[Reply]
Nice marco, worked well for me.
Question now is, how do you remove/disable it once you have used it? I do not see it showing listed in the AddIns area to remove/disable them.
[Reply]
Great macro. Worked wonders
How do i now remove it from the ribbon?
[Reply]
Thanks! I was afraid to download it at first, but it worked like charm!!!!!!!!! Awesome macro!
[Reply]
works… thnks m8
[Reply]
Thanks a Looooooooooot…
Helped me in time!
-arun
[Reply]
Thanks a lot for helped me. tnx.
[Reply]
Thank you! Worked in Excel 2007 perfectly and quickly
[Reply]
Thanks a lot for the information.
Worked grat on Excel 2007 + Win7.
Perfect.
Thanks from Portugal
[Reply]
Worked like a charm. Thanks a bunch.
[Reply]
Thanks a lot
Great Help.
[Reply]
Realy it works !!!!!!!!!!!!!
Thanks a lot
[Reply]
Wooow….!
Really great….!
Worked like Charm….!
Thanks lot….!
[Reply]
Thank you! You’re a genius. Note: not used for evil.
[Reply]
Great.. works fine
Thanks for sharing and Big Thanks for McGimpsey…
[Reply]
Thanks for this tool. I used it for legit reasons and it was very helpful.
[Reply]
how do u do it . i am having trouble?
[Reply]
Perfect.. I can unlock protected sheet..
thank you…
[Reply]
Can somebody please help me how to go about it step by step? I really really need it badly for work!! I put in a wrong password
[Reply]
To erika/ jow,
1) Just download the file and open it – enable the macros if prompted.
2)this will add a tool bar/button – Remove passwords
3) now open your workbook which has to be unprotected
4) click Remove passwords button to unprotect
hope it helps…
[Reply]
Slicker than gorilla snot…
Seriously, it works great, thanks for putting it out there.
[Reply]
I have a mac and was a little concerned that the unique version of Excel that I have would make the marco ineffective. Thank you so much, I am a novice with excel and this helped a great deal.
[Reply]
This really works well! It found the password very fast and unlocked the excel sheets. Thanks a lot.
[Reply]
Works like a charm! Thank you for you have saved me a lot of time. I´m using Excel 2007, and instead of disabling the password from the addins button, I just selected the allinternalpasswords macro in the macro section, with the same results. Got a pop-up with the pass, I disabled the protection, and went to town with the file
Thank you again.
[Reply]