Excel中撤销保护密码的操作方法.docx
《Excel中撤销保护密码的操作方法.docx》由会员分享,可在线阅读,更多相关《Excel中撤销保护密码的操作方法.docx(5页珍藏版)》请在冰豆网上搜索。
![Excel中撤销保护密码的操作方法.docx](https://file1.bdocx.com/fileroot1/2023-6/9/749904c8-d0f6-4800-b482-bf4f52d56e1f/749904c8-d0f6-4800-b482-bf4f52d56e1f1.gif)
Excel中撤销保护密码的操作方法
2021年Excel2021中撤销保护密码的操作方法
工作中,有时会出现这样的状况:
用Excel编制的报表、表格、程序等,在单元格中设置了公式、函数等,为了防止其别人修改您的设置或者防止您自己无意中修改,您可能会用法Excel的工作表爱护功能,但时间久了爱护密码简单遗忘,今日,我就教大家在Excel2021中撤销爱护密码的操作方法。
Excel2021中撤销爱护密码的操作步骤如下:
2021版Excel表格中可以根据以下方式建宏。
1、翻开Excel表格中的Excel选项,选择自定义,得到如下画面:
2、然后在左边侧框栏中选择"查看宏'
之后双击或者选择添加按钮,那么可以看到右边栏中有了查看宏按钮,之后点击右下角确实定。
3、大家可以在下面这个窗口处看到箭头所指的按钮:
点击按钮,之后弹出窗口:
4、在宏名处填写一个名字(可随便),然后点击创立,弹出以下窗口:
5、将窗口内的内容全选后删除,然后将下面分割线以内的内容开头复制粘贴在上面的窗口内:
从横线下开头复制
------------------------------------------------------------------------------------------
OptionExplicit
PublicSubAllInternalPasswords()
Breaksworksheetandworkbookstructurepasswords.BobMcCormick
probablyoriginatorofbasecodealgorithmmodifiedforcoverage
ofworkbookstructure/windowspasswordsandformultiplepasswords
NormanHarkerandJEMcGimpsey27-Dec-2021(Version1.1)
Modified2021-Apr-04byJEM:
Allmsgstoconstants,and
eliminateoneExitSub(Version1.1.1)
RevealshashedpasswordsNOToriginalpasswords
ConstDBLSPACEAsString=vbNewLinevbNewLine
ConstAUTHORSAsString=DBLSPACEvbNewLine_
AdaptedfromBobMcCormickbasecodeby_
NormanHarkerandJEMcGimpsey
ConstHEADERAsString=AllInternalPasswordsUserMessage
ConstVERSIONAsString=DBLSPACEVersion1.1.12021-Apr-04
ConstREPBACKAsString=DBLSPACEPleasereportfailure_
tothemicrosoft.public.excel.programmingnewsgroup.
ConstALLCLEARAsString=DBLSPACETheworkbookshould_
nowbefreeofallpasswordprotection,somakesureyou:
_
DBLSPACESAVEITNOW!
DBLSPACEandalso_
DBLSPACEBACKUP!
BACKUP!
!
BACKUP!
!
!
_
DBLSPACEAlso,rememberthatthepasswordwas_
putthereforareason.Dontstuffupcrucialformulas_
ordata.DBLSPACEAccessanduseofsomedata_
maybeanoffense.Ifindoubt,dont.
ConstMSGNOPWORDS1AsString=Therewerenopasswordson_
sheets,orworkbookstructureorwindows.AUTHORSVERSION
ConstMSGNOPWORDS2AsString=Therewasnoprotectionto_
workbookstructureorwindows.DBLSPACE_
Proceedingtounprotectsheets.AUTHORSVERSION
ConstMSGTAKETIMEAsString=AfterpressingOKbuttonthis_
willtakesometime.DBLSPACEAmountoftime_
dependsonhowmanydifferentpasswords,the_
passwords,andyourcomputersspecification.DBLSPACE_
Justbepatient!
Makemeacoffee!
AUTHORSVERSION
ConstMSGPWORDFOUND1AsString=YouhadaWorksheet_
StructureorWindowsPasswordset.DBLSPACE_
Thepasswordfoundwas:
DBLSPACE$$DBLSPACE_
Noteitdownforpotentialfutureuseinotherworkbooksby_
thesamepersonwhosetthispassword.DBLSPACE_
Nowtocheckandclearotherpasswords.AUTHORSVERSION
ConstMSGPWORDFOUND2AsString=YouhadaWorksheet_
passwordset.DBLSPACEThepasswordfoundwas:
_
DBLSPACE$$DBLSPACENoteitdownforpotential_
futureuseinotherworkbooksbysamepersonwho_
setthispassword.DBLSPACENowtocheckandclear_
otherpasswords.AUTHORSVERSION
ConstMSGONLYONEAsString=Onlystructure/windows_
protectedwiththepasswordthatwasjustfound._
ALLCLEARAUTHORSVERSIONREPBACK
Dimw1AsWorksheet,w2AsWorksheet
DimiAsInteger,jAsInteger,kAsInteger,lAsInteger
DimmAsInteger,nAsInteger,i1AsInteger,i2AsInteger
Dimi3AsInteger,i4AsInteger,i5AsInteger,i6AsInteger
DimPWord1AsString
DimShTagAsBoolean,WinTagAsBoolean
Application.ScreenUpdating=False
WithActiveWorkbook
WinTag=.ProtectStructureOr.ProtectWindows
EndWith
ShTag=False
ForEachw1InWorksheets
ShTag=ShTagOrw1.ProtectContents
Nextw1
IfNotShTagAndNotWinTagThen
MsgBoxMSGNOPWORDS1,vbInformation,HEADER
ExitSub
EndIf
MsgBoxMSGTAKETIME,vbInformation,HEADER
IfNotWinTagThen
MsgBoxMSGNOPWORDS2,vbInformation,HEADER
Else
OnErrorResumeNext
Dodummydoloop
Fori=65To66:
Forj=65To66:
Fork=65To66
Forl=65To66:
Form=65To66:
Fori1=65To66
Fori2=65To66:
Fori3=65To66:
Fori4=65To66
Fori5=65To66:
Fori6=65To66:
Forn=32To126
WithActiveWorkbook
.UnprotectChr(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=FalseAnd_
.ProtectWindows=FalseThen
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)
MsgBoxApplication.Substitute(MSGPWORDFOUND1,_
$$,PWord1),vbInformation,HEADER
ExitDoBypassallfor...nexts
EndIf
EndWith
Next:
Next:
Next:
Next:
Next:
Next
Next:
Next:
Next:
Next:
Next:
Next
LoopUntilTrue
OnErrorGoTo0
EndIf
IfWinTagAndNotShTagThen
MsgBoxMSGONLYONE,vbInformation,HEADER
ExitSub
EndIf
OnErrorResumeNext
ForEachw1InWorksheets
AttemptclearancewithPWord1
w1.UnprotectPWord1
Nextw1
OnErrorGoTo0
ShTag=False
ForEachw1InWorksheets
ChecksforallclearShTagtriggeredto1ifnot.
ShTag=ShTagOrw1.ProtectContents
Nextw1
IfShTagThen
ForEachw1InWorksheets
Withw1
If.ProtectContentsThen
OnErrorResumeNext
DoDummydoloop
Fori=65To66:
Forj=65To66:
Fork=65To66
Forl=65To66:
Form=65To66:
Fori1=65To66
Fori2=65To66:
Fori3=65To66:
Fori4=65To66
Fori5=65To66:
Fori6=65To66:
Forn=32To126
.UnprotectChr(i)Chr(j)Chr(k)_
Chr(l)Chr(m)Chr(i1)Chr(i2)Chr(i3)_
Chr(i4)Chr(i5)Chr(i6)Chr(n)
IfNot.ProtectContentsThen
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)
MsgBoxApplication.Substitute(MSGPWORDFOUND2,_
$$,PWord1),vbInformation,HEADER
leveragefindingPwordbytryingonothersheets
ForEachw2InWorksheets
w2.UnprotectPWord1
Nextw2
ExitDoBypassallfor...nexts
EndIf
Next:
Next:
Next:
Next:
Next:
Next
Next:
Next:
Next:
Next:
Next:
Next
LoopUntilTrue
OnErrorGoTo0
EndIf
EndWith
Nextw1
EndIf
MsgBoxALLCLEARAUTHORSVERSIONREPBACK,vbInformation,HEADER
EndSub
-----------------------------------------------------------------------------------------
复制到横线以上
得到状况如下列图表示
6、关闭编辑窗口;
7、依次点击菜单栏上的工具---宏-----宏,选AllInternalPasswords,运行,确定两次;
等一会,就会出现以下对话框:
这就是Excel密码对应的原始密码(此密码和原先设置的密码都能翻开此文档。
假如是别人的文档,你又想复原密码设置,就可以用此密码进展爱护,他就能用他设置的密码翻开,你可以试试,很好玩的。
字母肯定要大写):
再点击确定。
Excel的原始密码就被去除了!
!
8、最终记得关闭EXCEL文档时要保存,否那么下回翻开,文档照旧处于只读状态而无法修改。