Tony,

See below for a post from Norman Harker that shows a technique that can be
used - you need to be familiar with macros....

HTH,
Bernie
MS Excel MVP


Hi Tom!

Thanks for that. Now to stop Denis from being fired by providing the code
plus step by step instructions for how to use it.


Here's a standard collation of password recovery facilities.


Please note that unauthorized use of password recovery techniques can breach
software license agreements and leave you without otherwise available
assistance and remedies. You may also be liable to being sued or even
prosecuted under the terms of the software license. You can in some cases be
in breach of terms of your employment contract and find yourself out on the
street.


You are also warned that civil and criminal proceedings risks should not be
viewed lightly. If in any doubt, don't! Most, if not all, software
providers,
will assist by email, free of charge, genuine cases where users have lost
passwords and/or registration codes.


Anyone who has comments or sites to add please feel free to respond.


Obviously, this post comes with a complete money back guarantee. (You paid
none; you'll get none back!)


The real lessons to be learned? Keep a track of your passwords. Look after
your employees and they'll not leave without telling you the passwords!
Excel is not, and has never been made out to be, secure as a mode of storing
data.


Worksheet and Workbook (*Not* Protected (Un-openable) files).


http://www.straxx.com/excel/password.html
Free of charge but requests a donation. Excel 5.0 up to XP. They've been
going for years and it does reveal *a* password rather than *the* password


For "Internal to Excel Worksheet and Workbook passwords up to 12 characters,
the following code usually works. It's from an original by Bob McCormick
that I've amended to cover workbook passwords (not to be confused with
workbook file passwords). It seems to work OK for both as long as there is
only one password used. The code finds *a* password, not *the* password,
issues a report and leaves the workbook / worksheet unprotected. In it's
original (worksheet password only) form, you could use the reported
worksheet password to remove a workbook password if it was the same
password.


****


Sub InternalPasswords()
'Original (?)by Bob McCormick on NG:
' microsoft.public.excel.misc on 22 May 2001.
'Adapted for workbook passwords Norman Harker 19 Nov 2002
'Breaks workbook and worksheet password protection.
'Works for either workbook or worksheet passwords or both
' if the passwords are the same.
'If passwords are different then
' systematically "comment out" the nested if functions


Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer


On Error Resume Next
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
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)
ActiveSheet.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 ActiveWorkbook.ProtectStructure = False Then
If ActiveWorkbook.ProtectWindows = False Then
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) _
& Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) _
& Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
End If
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub


****


If this doesn't work, then it could be that there is more than one password
used so you will have to modify the code by commenting out the IF function
block of the code. This is done by simply putting a ' in front of the bits
to be commented out.


Here is the commented out code that allows use for removal of the worksheet
password:


'If ActiveWorkbook.ProtectStructure = False Then
'If ActiveWorkbook.ProtectWindows = False Then
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) _
& Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) _
& Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
'End If
'End If


Here is the commented out code that allows use for removal of the workbook
password:


If ActiveWorkbook.ProtectStructure = False Then
If ActiveWorkbook.ProtectWindows = False Then
'If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) _
& Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) _
& Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
'End If
End If
End If


But how do I use this code? Here are instructions aimed at those who don't
know how to get this code into their workbooks:


1. Print out a copy of these instructions (not compulsory but it will make
it
easier)


2. Keep this newsgroup posting open.


3. Open Excel
4. Open the "offending" workbook
5. Press and hold down the Alt key
6. Press F11
7. Let go the Alt key


You're now in the Visual Basic Editor (VBE)
On left of screen is the Project Explorer


8. Select the name of your workbook
9. Right click Insert Module


There should now be a blank screen on the right with two drop downs above.


10. Click this post's icon on your task bar
11. Press the maximize button to get it to full screen if it isn't already.
(middle of top right three buttons)


11. Select all of the code above between **** (but not the ****)
12. Edit Copy (or press copy icon or right click copy)


13. Activate the Visual Basic Editor screen icon on your task bar.
14. Click in that blank screen area
15. Right click Paste


16. Click on the Offending Files icon on your task bar (or left-most Excel
icon
in the Visual Basic Editor toolbar)


You should be in your workbook now.


17. Tools Macro Macros
18. From the list select:
InternalPasswords
19. Click the button marked Run
20. Wait


You should get a message saying its found a password but don't worry about
that because after you click the OK button you'll find if you use Tools
Protection that the workbook and worksheets are not protected.


21. Breathe sighs of relief!


In case you're thinking of trying it, the passwords reported for worksheet
and workbook protection do not work for removing VBA or Excel Workbook file
protection.


If and when you have success or failure with the above, please post back to
the newsgroup as it helps others to see that these things work or not.


HTH


--
Norman Harker









"CoachTony" wrote in message
...
Hello,

One of my users has a worksheet that was created by a former employee,
which
is password protected.

They now want to add some functionality to that worksheet but cannot
becuase
they do not know the password the former employee used.

Is there a way to change the password thru VBA?

As always, I appreciate any help you can provide.
--
Coach Tony