Excel VBA Tips: Remove Password from Worksheet

Excel VBA Tips: Remove Password from Worksheet

There have been many times when I’ve password protected an excel worksheet or workbook and then forgotten what it was. Wanting to modify the sheet later, I then realise I can’t, as I don’t remember what the password is! However, there is a way around this!

Remove worksheet or workbook password

Before outlining how to do this, this process is only to be used to edit a file which you created. It is not to be used for the hacking of files created by others.

  1. Create a new macro.
  2. Inside the macro, paste the following code submitted by brettdj on www.experts-exchange.com.

Sub PasswordBreaker()
‘Author unknown but submitted by brettdj of http://www.experts-exchange.com

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

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 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)
ActiveWorkbook.Sheets(1).Select
Range(“a1″).FormulaR1C1 = 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
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next

End Sub

  1. Go back to the workbook and navigate to the worksheet that you wish to access. Click on the Developer Tab and go to macros (Excel 2007) or Tools > Macro > Macros (Excel 2003). Double click on the PasswordBreaker macro.
  2. The password is now removed and you are free to edit the sheet. You are also able to enter a new password, but remember it!

Come check out the directory for the rest of my excel tips!

Submeg

IT

About these ads

Tell me what you think!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Views:

    • 291,473 hits
  • submeg.fm: 30,377 songs 6,160 artists
    AND GROWING...
Follow

Get every new post delivered to your Inbox.

Join 847 other followers

%d bloggers like this: