excel20XX工作表如何撤销保护

excel20XX工作表如何撤销保护

篇一：如何撤销excel工作表保护

exceL工作表编辑资料，设置了工作表保护后，不能对表格进行操作。如果没有密码，很简单:工具-选项—工作表保护——撤消工作表保护就可以了。如果忘记密码或者不知道密码，执行如下操作：

1、打开文件

2、工具---宏----录制新宏---输入名字如:a

3、停止录制(这样得到一个空宏)

4、工具---宏----宏,选a,点编辑按钮

5、删除窗口中的所有字符(只有几个),替换为下面的内容:(复制下来) optionexplicit

publicsubAllInternalpasswords()

'breaksworksheetandworkbookstructurepasswords.bobmccormick

'probablyoriginatorofbasecodealgorithmmodifiedforcoverage

'ofworkbookstructure/windowspasswordsandformultiplepasswords

'

'normanharkerandJemcgimpsey27-Dec-20XX(Version1.1)

'modified20XX-Apr-04byJem:Allmsgstoconstants,and

'eliminateoneexitsub(Version1.1.1)

'RevealshashedpasswordsnoToriginalpasswords

constDbLspAceAsstring=vbnewLine&vbnewLine constAuThoRsAsstring=DbLspAce&vbnewLine&_ "Adaptedfrombobmccormickbasecodeby"&_ "normanharkerandJemcgimpsey"

constheADeRAsstring="AllInternalpasswordsusermessage" constVeRsIonAsstring=DbLspAce&"Version1.1.120XX-Apr-04" constRepbAcKAsstring=DbLspAce&"pleasereportfailure"&_ "tothemicrosoft.public.excel.programmingnewsgroup." constALLcLeARAsstring=DbLspAce&"Theworkbookshould"&_ "nowbefreeofallpasswordprotection,somakesureyou:"&_ DbLspAce&"sA VeITnow!"&DbLspAce&"andalso"&_ DbLspAce&"bAcKup!,bAcKup!!,bAcKup!!!"&_

DbLspAce&"Also,rememberthatthepasswordwas"&_ "putthereforareason.Don'tstuffupcrucialformulas"&_ "ordata."&DbLspAce&"Accessanduseofsomedata"&_ "maybeanoffense.Ifindoubt,don't."

constmsgnopwoRDs1Asstring="Therewerenopasswordson"&_ "sheets,orworkbookstructureorwindows."&AuThoRs&VeRsIon constmsgnopwoRDs2Asstring="Therewasnoprotectionto"&_ "workbookstructureorwindows."&DbLspAce&_ "proceedingtounprotectsheets."&AuThoRs&VeRsIon constmsgTAKeTImeAsstring="AfterpressingoKbuttonthis"&_

"willtakesometime."&DbLspAce&"Amountoftime"&_ "dependsonhowmanydifferentpasswords,the"&_ "passwords,andyourcomputer'sspecification."&DbLspAce&_ "Justbepatient!makemeacoffee!"&AuThoRs&VeRsIon constmsgpwoRDFounD1Asstring="Youhadaworksheet"&_ "structureorwindowspasswordset."&DbLspAce&_ "Thepasswordfoundwas:"&DbLspAce&"$$"&DbLspAce&_ "noteitdownforpotentialfutureuseinotherworkbooksby"&_ "thesamepersonwhosetthispassword."&DbLspAce&_ "nowtocheckandclearotherpasswords."&AuThoRs&VeRsIon constmsgpwoRDFounD2Asstring="Youhadaworksheet"&_ "passwordset."&DbLspAce&"Thepasswordfoundwas:"&_ DbLspAce&"$$"&DbLspAce&"noteitdownforpotential"&_ "futureuseinotherworkbooksbysamepersonwho"&_ "setthispassword."&DbLspAce&"nowtocheckandclear"&_ "otherpasswords."&AuThoRs&VeRsIon constmsgonLYoneAsstring="onlystructure/windows"&_ "protectedwiththepasswordthatwasjustfound."&_ ALLcLeAR&AuThoRs&VeRsIon&RepbAcK

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

Do'dummydoloop

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

exitDo'bypassallfor...nexts

endIf

endwith

next:next:next:next:next:next

next:next:next:next:next:next

LoopuntilTrue

onerrorgoTo0

endIf