設(shè)置excell表格或單元格格式固定,不讓別人修改,保護(hù)工作表,包含破解方法

1、設(shè)置excell表格或單元格格式固定，不讓別人修改，保護(hù)工作表，包含破解方法一、設(shè)置方法1、舉個例子，下面是一般的excell表格，每個單元格都可以改動2、如果我們只想讓別人修改姓名下面的單元格，其他不能動的話，先選擇可以修改的單元格B4、B5（有多個單元格的話按Ctrl+單元格連續(xù)選擇）點(diǎn)右鍵，選擇設(shè)置單元格格式3、選擇“保護(hù)”選項卡，把“鎖定”前面的勾去掉，然后確定4、右鍵單擊下面的“工作表標(biāo)簽”，選擇“保護(hù)工作表”5、輸入密碼，其他的不用管，確定6、再輸入一次剛才的密碼，確定7、這時候設(shè)置成功了，如果點(diǎn)擊別的單元格想修改的話會出現(xiàn)以下提示，這樣就可以放心的發(fā)給別人填表了。8、如果想撤銷保

2、護(hù)就右鍵單擊下方的工作表標(biāo)簽，選擇撤銷工作表保護(hù)9、輸入設(shè)置的密碼就能取消保護(hù)了二、破解方法1、如果忘記了密碼怎么辦，自己也不能編輯了那不是沒招了嗎？我們還可以有辦法，適用于“首次忘記密碼撤銷保護(hù)”，先隨便選一個單元格，然后點(diǎn)擊excel菜單里的“視圖”-“宏”-“錄制宏”2、宏名隨便起一個“ss”，確定3、點(diǎn)擊excel菜單里的“視圖”-“宏”-“停止錄制”就得到了一個空宏-ss4、點(diǎn)擊excel菜單里的“視圖”-“宏”-“查看宏”5、選擇編輯6、刪除掉所有的字符，紅框內(nèi)的7、復(fù)制下面的代碼進(jìn)入，注意：一個字符都不能少，不能錯，如下圖，然后關(guān)閉編輯窗口回到excel表Option Expli

3、citPublic 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-2

4、002 (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 passwordsConst DBLSPACE As String = vbNewLine & vbNewLineConst AUTHORS As String = DBLSPACE & vbNewLine & _"Adapted f

5、rom 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 failu

6、re " & _"to the gramming 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 &

7、 "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

8、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 & VERSIONConst MSGNOPWORDS2 As String = "There was no protec

9、tion to " & _"workbook structure or windows." & DBLSPACE & _"Proceeding to unprotect sheets." & AUTHORS & VERSIONConst MSGTAKETIME As String = "After pressing OK button this " & _"will take some time." & DBLSPACE & "Am

10、ount of time " & _"depends on how many different passwords, the " & _"passwords, and your computer's specification." & DBLSPACE & _"Just be patient! Make me a coffee!" & AUTHORS & VERSIONConst MSGPWORDFOUND1 As String = "You had a W

11、orksheet " & _"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

12、 password." & DBLSPACE & _"Now to check and clear other passwords." & AUTHORS & VERSIONConst MSGPWORDFOUND2 As String = "You had a Worksheet " & _"password set." & DBLSPACE & "The password found was: " & _DBLSPACE & &qu

13、ot;$" & 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 & VERSIONC

14、onst MSGONLYONE As String = "Only structure / windows " & _"protected with the password that was just found." & _ALLCLEAR & AUTHORS & VERSION & REPBACKDim w1 As Worksheet, w2 As WorksheetDim i As Integer, j As Integer, k As Integer, l As IntegerDim m As Intege

15、r, n As Integer, i1 As Integer, i2 As IntegerDim i3 As Integer, i4 As Integer, i5 As Integer, i6 As IntegerDim PWord1 As StringDim ShTag As Boolean, WinTag As BooleanApplication.ScreenUpdating = FalseWith ActiveWorkbookWinTag = .ProtectStructure Or .ProtectWindowsEnd WithShTag = FalseFor Each w1 In

16、WorksheetsShTag = ShTag Or w1.ProtectContentsNext w1If Not ShTag And Not WinTag ThenMsgBox MSGNOPWORDS1, vbInformation, HEADERExit SubEnd IfMsgBox MSGTAKETIME, vbInformation, HEADERIf Not WinTag ThenMsgBox MSGNOPWORDS2, vbInformation, HEADERElseOn Error Resume NextDo 'dummy do loopFor i = 65 To

17、66: For j = 65 To 66: For k = 65 To 66For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126With ActiveWorkbook.Unprotect Chr(i) & Chr(j) & Chr(k) & _Chr(l) & Chr(m) & Chr

18、(i1) & Chr(i2) & _Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)If .ProtectStructure = False And _.ProtectWindows = False ThenPWord1 = Chr(i) & Chr(j) & Chr(k) & Chr(l) & _Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _Chr(i4) & Chr(i5) & Chr(

19、i6) & Chr(n)MsgBox Application.Substitute(MSGPWORDFOUND1, _"$", PWord1), vbInformation, HEADERExit Do 'Bypass all for.nextsEnd IfEnd WithNext: Next: Next: Next: Next: NextNext: Next: Next: Next: Next: NextLoop Until TrueOn Error GoTo 0End IfIf WinTag And Not ShTag ThenMsgBox MSGONL

20、YONE, vbInformation, HEADERExit SubEnd IfOn Error Resume NextFor Each w1 In Worksheets'Attempt clearance with PWord1w1.Unprotect PWord1Next w1On Error GoTo 0ShTag = FalseFor Each w1 In Worksheets'Checks for all clear ShTag triggered to 1 if not.ShTag = ShTag Or w1.ProtectContentsNext w1If Sh

21、Tag ThenFor Each w1 In WorksheetsWith w1If .ProtectContents ThenOn Error Resume NextDo 'Dummy do loopFor i = 65 To 66: For j = 65 To 66: For k = 65 To 66For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66For 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 The