CARA MEMBUKA SHEET BERIKUTNYA PADA FILE EXCEL DENGAN KEYBOARD

Excel is a powerful tool for data management and calculations. However, sometimes we encounter locked or protected sheets that make it challenging to perform necessary operations. But worry no more! In this article, we will explore the tips and tricks to unlock or unprotect Excel sheets with ease.

How to Unlock a Protected Sheet in Excel?

When an Excel sheet is protected, it means that some operations or cells are restricted by the worksheet’s creator. It can be frustrating and time-consuming to request access from the person who created the worksheet. So, how do you unlock the protected sheet?

Method 1: Using Password

The most common way to protect an Excel sheet is by using a password. Here’s how to remove the password:

1. Open the Excel sheet and click on File > Save As.
2. Select a location to save the file and type a new name for the file (optional).
3. In the “Save as type” dropdown, select “Excel Workbook (or Macro-Enabled Workbook)” and click on “Save.”
4. A confirmation message will appear asking if you want to replace the existing file. Click on “Yes.”
5. Now, close the Excel sheet and navigate to the saved location.
6. Right-click on the saved file and select “Rename.”
7. Change the file type from .xlsx to .zip (e.g., “filename.zip”).
8. Open the zipped file and navigate to the “xl” folder.
9. Open the “worksheets” folder and locate the sheet that is password-protected (e.g., “sheet1.xml”).
10. Right-click on the sheet and select “Edit.”
11. Search for this line of code: “<sheetProtection password=’PASSWORD’ sheet=’1′ />”
12. Delete the “password=’PASSWORD'” section and save the file.
13. Close the file and rename the extension from .zip to .xlsx (e.g., “filename.xlsx”).
14. Reopen the file in Excel, and the sheet should now be unlocked.

If the sheet is still protected and you have the correct password, follow this alternative method:

1. Open the Excel sheet and click on “Review” > “Unprotect Sheet.”
2. Enter the password and click on “OK.”
3. The sheet is now unprotected, and you can perform necessary operations.

Method 2: Using VBA Editor

If Method 1 doesn’t work, you can unlock the sheet using VBA Editor. The steps are as follows:

1. Open the Excel sheet and press “Alt” + “F11” to open the VBA Editor.
2. In the VBA Editor, click on “Insert” > “Module.”
3. Paste the following code into the module:

Sub PasswordBreaker()
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 = 66 To 67: For k = 68 To 69
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

4. Click on “Run” > “Run Sub/UserForm” or press “F5.”
5. The code will generate usable passwords. Note down the password that works and close the VBA Editor.
6. Open the Excel sheet and click on “Review” > “Unprotect Sheet.”
7. Enter the password and click on “OK.”
8. The sheet is now unprotected, and you can perform necessary operations.

How to Remove Password from an Excel Sheet?

If you have an Excel sheet with a password, it means that some operations or cells are restricted by the worksheet’s creator. However, it is possible to remove the password entirely. Here’s how:

Method 1: Using “Save As” Option

1. Open the password-protected Excel sheet.
2. Click on “File” > “Save As.”
3. Select a location to save the file and type a new name for the file (optional).
4. In the “Save as type” dropdown, select “Excel Workbook (or Macro-Enabled Workbook)” and click on “Save.”
5. A confirmation message will appear asking if you want to replace the existing file. Click on “Yes.”
6. Close the original Excel sheet and navigate to the saved location.
7. Open the newly saved file.
8. Click on “File” > “Info” > “Protect Workbook” > “Encrypt with Password.”
9. Delete the password in the “Password” field and click on “OK.”
10. Save the file and close it.
11. Reopen the file in Excel, and the sheet should now be unprotected.

Method 2: Using VBA Editor

If Method 1 doesn’t work, you can remove the password using VBA Editor. The steps are as follows:

1. Open the Excel sheet and press “Alt” + “F11” to open the VBA Editor.
2. In the VBA Editor, click on “Insert” > “Module.”
3. Paste the following code into the module:

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 = 66 To 67: For k = 68 To 69
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
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
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

4. Click on “Run” > “Run Sub/UserForm” or press “F5.”
5. The code will generate usable passwords. Note down the password that works and close the VBA Editor.
6. Open the Excel sheet and click on “File” > “Info” > “Protect Workbook” > “Encrypt with Password.”
7. Delete the password in the “Password” field and click on “OK.”
8. Save the file and close it.
9. Reopen the file in Excel, and the sheet should now be unprotected.

FAQ

Q1: Can I unlock an Excel sheet without a password?

A1: Unfortunately, if an Excel sheet is password-protected, you cannot unlock it without the correct password. However, you can use VBA Editor to generate usable passwords that work.

Q2: Is it legal to remove the password from an Excel sheet?

A2: It is legal to remove the password from an Excel sheet if you are the owner and have the right to do so. However, it is illegal to remove passwords from someone else’s Excel sheet without their permission.

Conclusion

Unlocking a protected or password-protected Excel sheet can be a challenge but using the methods outlined in this article should make it easier. Whether you use a password or VBA Editor, the steps are simple and can be done in a matter of minutes. If you encounter any issues in unlocking or unprotecting an Excel sheet, feel free to contact us.

And lastly, if you prefer watching videos over reading articles, here’s a tutorial video on how to unlock and unprotect Excel sheets: