学习啦 > 学习电脑 > 工具软件 > 办公软件学习 > Excel教程 > Excel表格 > excel表格取消保护的方法

excel表格取消保护的方法

时间: 业华773 分享

excel表格取消保护的方法

  Excel中经常需要使用到保护功能进行保护数据,但需要取消保护的时候具体该如何操作呢?接下来是学习啦小编为大家带来的excel表格取消保护的方法,供大家参考。

  excel表格取消保护的方法:

  取消保护步骤1:视图—宏—录制新宏—输入宏名如:aa(aa是可随意输入)

  取消保护步骤2:停止录制,这样得到一个空宏

学习啦在线学习网   取消保护步骤3:同样视图—宏—查看宏—选aa(aa为之前新建的宏)—点击编辑按钮

  取消保护步骤4:删除窗口中的所有字符,复制下面的内容粘贴。一个字母、标点符号都不能少。

  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

  '

学习啦在线学习网   ' Norman 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 NOT original passwords

  Const DBLSPACE As String = vbNewLine & vbNewLine

学习啦在线学习网   Const AUTHORS As String = DBLSPACE & vbNewLine & _

学习啦在线学习网   "Adapted from Bob McCormick base code by" & _

  "Norman Harker and JE McGimpsey"

  Const HEADER As String = "AllInternalPasswords User Message"

学习啦在线学习网   Const VERSION As String = DBLSPACE & "Version 1.1.1 2003-Apr-04"

学习啦在线学习网   Const REPBACK As String = DBLSPACE & "Please report failure " & _

  "to the microsoft.public.excel.programming newsgroup."

  Const ALLCLEAR As String = DBLSPACE & "The workbook should " & _

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

  DBLSPACE & "SAVE IT NOW!" & 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 MSGNOPWORDS1 As String = "There were no passwords on " & _

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

  Const MSGNOPWORDS2 As String = "There was no protection to " & _

  "workbook structure or windows." & DBLSPACE & _

  "Proceeding to unprotect sheets." & AUTHORS & VERSION

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

学习啦在线学习网   Const MSGPWORDFOUND1 As String = "You had a Worksheet " & _

学习啦在线学习网   "Structure or Windows Password set." & DBLSPACE & _

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

学习啦在线学习网   "Note it down for potential future use in other workbooks by " & _

学习啦在线学习网   "the same person who set this password." & DBLSPACE & _

  "Now to check and clear other passwords." & AUTHORS & VERSION

学习啦在线学习网   Const MSGPWORDFOUND2 As String = "You had a Worksheet " & _

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

学习啦在线学习网   DBLSPACE & "$$" & DBLSPACE & "Note it down for potential " & _

学习啦在线学习网   "future use in other workbooks by same person who " & _

学习啦在线学习网   "set this password." & DBLSPACE & "Now to check and clear " & _

  "other passwords." & AUTHORS & VERSION

  Const MSGONLYONE As String = "Only structure / windows " & _

学习啦在线学习网   "protected with the password that was just found." & _

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

学习啦在线学习网   Application.ScreenUpdating = False

学习啦在线学习网   With ActiveWorkbook

  WinTag = .ProtectStructure Or .ProtectWindows

学习啦在线学习网   End With

学习啦在线学习网   ShTag = False

  For Each w1 In Worksheets

  ShTag = ShTag Or w1.ProtectContents

  Next w1

  If Not ShTag And Not WinTag Then

学习啦在线学习网   MsgBox MSGNOPWORDS1, vbInformation, HEADER

学习啦在线学习网   Exit Sub

  End If

  MsgBox MSGTAKETIME, vbInformation, HEADER

  If Not WinTag Then

学习啦在线学习网   MsgBox MSGNOPWORDS2, vbInformation, HEADER

  Else

  On Error Resume Next

学习啦在线学习网   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 Application.Substitute(MSGPWORDFOUND1, _

  "$$", PWord1), vbInformation, HEADER

学习啦在线学习网   Exit Do 'Bypass all for...nexts

  End If

  End With

  Next: Next: Next: Next: Next: Next

  Next: Next: Next: Next: Next: Next

  Loop Until True

学习啦在线学习网   On Error GoTo 0

  End If

  If WinTag And Not ShTag Then

学习啦在线学习网   MsgBox MSGONLYONE, vbInformation, HEADER

  Exit Sub

  End If

学习啦在线学习网   On Error Resume Next

  For Each w1 In Worksheets

学习啦在线学习网   'Attempt clearance with PWord1

学习啦在线学习网   w1.Unprotect PWord1

  Next 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 w1.ProtectContents

  Next w1

  If ShTag Then

学习啦在线学习网   For Each w1 In Worksheets

  With w1

  If .ProtectContents Then

  On Error Resume Next

学习啦在线学习网   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 Not .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 Application.Substitute(MSGPWORDFOUND2, _

  "$$", PWord1), vbInformation, HEADER

学习啦在线学习网   'leverage finding Pword by trying on other sheets

  For Each w2 In Worksheets

学习啦在线学习网   w2.Unprotect PWord1

  Next w2

学习啦在线学习网   Exit Do 'Bypass all for...nexts

  End If

  Next: Next: Next: Next: Next: Next

学习啦在线学习网   Next: Next: Next: Next: Next: Next

  Loop Until True

  On Error GoTo 0

  End If

学习啦在线学习网   End With

  Next w1

  End If

  MsgBox ALLCLEAR & AUTHORS & VERSION & REPBACK, vbInformation, HEADER

  End Sub

  取消保护步骤5:关闭编辑窗口

学习啦在线学习网   取消保护步骤6:视图—宏—查看宏,选AllInternalPasswords,点击执行,确定两次,等2分钟,再确定。密码撤销完毕。

900004