Technology Coaching: Protecting Cells In A Worksheet In

THis document will tel you how to unprotect an excel spread sheet without having the password This procedure works in Excel 2010 and earlier but in Excel 2013 this will not work. In case of a password protect worksheet you are unable to Edit the data on the Excel Sheet. If you do not Remember the Password or do not know the password to unprotect the sheet just follow the below simple steps. Press ALT + F11 or click on View Code in Developers Tabs In the Above White Space Enter the below Code.

Do not change the code just copy paste: Sub PasswordBreaker 'Breaks worksheet password protection. 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) Exit Sub End If Next: Next: Next: Next: Next: Next Next: Next: Next: Next: Next: Next End Sub Now Click on the Run Button or press F5: And there you go the sheet is unprotected for you now. Also you would be getting a message in the pop up window. This Message is contains the password which can be used to unprotect the other sheets in the same workbook.

Technology Coaching Protecting Cells In A Worksheet In The

This post is inspired by the previous post Situation Suppose you are going to share an Excel workbook to your colleagues. However there is a worksheet with sensitive data that you don’t want them to see. So you hide the worksheet. As you know Excel well, you further Protect Workbook, with password set, in order to avoid user from unhiding the worksheet. Since you are also a careful person, you test to make sure no one can unhide the worksheet unless one knows the password to unprotect workbook: Now you are pretty sure that no one can unhide the worksheet, without the password. BUT ARE YOU SURE NO ONE CAN SEE THE DATA ON IT? Here’s a simple trick to look into the data on the hidden sheet.

Technology Coaching: Protecting Cells In A Worksheet In Word

Indeed I don’t even need to unhide the worksheet to get the data on it. Did you notice that the name of the hidden worksheet was disclosed in the VB Editor? That’s the window you saw after clicking “View Code” in the above screencast. From there, I know that the Hidden Sheet is called “ Secret“. With this little piece of information, anyone can retrieve the data on any cell on the worksheet easily. A picture tells thousand words; a sceencast tells even more 🙂 See?!

What I did was just a simple formula of referencing: =Secret!A1 Secret No More!!! Conclusion Please don’t get me wrong. I am not encouraging you to hack into data that is not intended for you. My point here is: DO NOT include sensitive/confidential/private data in a workbook at all, if the recipients are not supposed to read that. Hi Micky, I just tried in Excel 2013. It works too.

In the VBE, go to View – Immediate Window. Or simply Ctrl+G (if the Immediate Window is not visible). I can see and work with the Immediate Window as usual. Moreover, I can open an new workbook and use VBA to get all worksheets’ name (hidden, very hidden, or visible) of another workbook successfully. Having said that, as concluded in my blogpost, it’s not about how to get the data on a hidden sheet. It’s about NOT to consider “hiding” something in a workbook as a secure way to protect data from being seen. Side topic: I found sth strange in my trial with the VBE When I tried to lock the VBProject by right-clicking it, Properties, Protection, Lock from Viewing, passwords OK.

Saved the file. Then the password is GONE. NO protection at all. Then I tried again and saved it as xlsm.

Technology Coaching Protecting Cells In A Worksheet In The Format Cells

Still didn’t work. Then I inserted a blank module to the VBProject, then it worked Strange? As I said, I am not good in VBA, maybe I missed something that I don’t know.