Excel中表格工作表保护密码撤销的操作方法

Excel中表格工作表保护密码撤销的操作方法

在做excel表格时我们有时候会给自己的工作表加密，一旦忘记密

码就会很麻烦，如何绕过密码，直接撤销保护。今天，店铺就教大家

在Excel中表格工作表保护密码撤销的操作方法。

Excel中表格工作表保护密码撤销的操作步骤

打开excel，点击菜单栏的“视图”，点击二级菜单里的“宏”，

选择录制宏。

表格工作表保护密码撤销的操作图1

在弹出来的方框中输入宏的名称，点击确认。

表格工作表保护密码撤销的操作图2

重复第一步的步骤，不过这次点击“停止录制”。

表格工作表保护密码撤销的操作图3

选择查看宏。

表格工作表保护密码撤销的操作图4

编辑宏。

表格工作表保护密码撤销的操作图5

在弹出来的代码框输入下面的代码：Option Explicit

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

'

' orman 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 OT original passwords

Const DBLSPACE As String = vbewLine & vbewLine

Const AUTHORS As String = DBLSPACE & vbewLine & _

"Adapted from Bob McCormick base code by" & _

"orman Harker and JE McGimpsey"

Const HEADER As String = "AllInternalPasswords User

Message"

Const VERSIO As String = DBLSPACE & "Version 1.1.1 2003-

Apr-04"

Const REPBACK As String = DBLSPACE & "Please report

failure " & _

"to the mming newsgroup."

Const ALLCLEAR As String = DBLSPACE & "The workbook

should " & _

"now be free of all password protection, so make sure you:"

& _

DBLSPACE & "SAVE IT OW!" & 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 MSGOPWORDS1 As String = "There were no

passwords on " & _

"sheets, or workbook structure or windows." & AUTHORS &

VERSIO

Const MSGOPWORDS2 As String = "There was no

protection to " & _

"workbook structure or windows." & DBLSPACE & _

"Proceeding to unprotect sheets." & AUTHORS & VERSIO

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 & VERSIO

Const MSGPWORDFOUD1 As String = "You had a

Worksheet " & _

"Structure or Windows Password set." & DBLSPACE & _

"The password found was: " & DBLSPACE & "" & DBLSPACE

& _

"ote it down for potential future use in other workbooks by

" & _

"the same person who set this password." & DBLSPACE & _

"ow to check and clear other passwords." & AUTHORS &

VERSIO

Const MSGPWORDFOUD2 As String = "You had a

Worksheet " & _

"password set." & DBLSPACE & "The password found was: "

& _

DBLSPACE & "" & DBLSPACE & "ote it down for potential "

& _

"future use in other workbooks by same person who " & _

"set this password." & DBLSPACE & "ow to check and clear

" & _

"other passwords." & AUTHORS & VERSIO

Const MSGOLYOE As String = "Only structure / windows

" & _

"protected with the password that was just found." & _

ALLCLEAR & AUTHORS & VERSIO & 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

Updating = False

With ActiveWorkbook

WinTag = .ProtectStructure Or .ProtectWindows

End With

ShTag = False

For Each w1 In Worksheets

ShTag = ShTag Or tContents

ext w1

If ot ShTag And ot WinTag Then

MsgBox MSGOPWORDS1, vbInformation, HEADER

Exit Sub

End If

MsgBox MSGTAKETIME, vbInformation, HEADER

If ot WinTag Then

MsgBox MSGOPWORDS2, vbInformation, HEADER

Else

On Error Resume ext

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 tute(MSGPWORDFOUD1, _

"", PWord1), vbInformation, HEADER

Exit Do 'Bypass s

End If

End With

ext: ext: ext: ext: ext: ext

ext: ext: ext: ext: ext: ext

Loop Until True

On Error GoTo 0

End If

If WinTag And ot ShTag Then

MsgBox MSGOLYOE, vbInformation, HEADER

Exit Sub

End If

On Error Resume ext

For Each w1 In Worksheets

'Attempt clearance with PWord1

ect PWord1

ext 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 tContents

ext w1

If ShTag Then

For Each w1 In Worksheets

With w1

If .ProtectContents Then

On Error Resume ext

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 ot .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 tute(MSGPWORDFOUD2, _

"", PWord1), vbInformation, HEADER

'leverage finding Pword by trying on other sheets

For Each w2 In Worksheets

ect PWord1

ext w2

Exit Do 'Bypass s

End If

ext: ext: ext: ext: ext: ext

ext: ext: ext: ext: ext: ext

Loop Until True

On Error GoTo 0

End If

End With

ext w1

End If

MsgBox ALLCLEAR & AUTHORS & VERSIO & REPBACK,

vbInformation, HEADER

End Sub

表格工作表保护密码撤销的操作图6