Improve company productivity with a Business Account.Sign Up

x
?
Solved

Can't unprotect sheet in Excel 2K

Posted on 2004-04-07
9
Medium Priority
?
2,874 Views
Last Modified: 2012-05-04
I have an Excel workbook with sheets and the project protected by passwords to prevent people from putting data in the wrong place, which would prevent its extraction using a corresponding VB application.

One of the macros is set up to run when a worksheet is activated. First it unprotects the sheet, then it formats and copies summary information from one of the other worksheets, and finally it protects the sheet again.

At the moment, however, when I try to select that worksheet I get:
Run-time error '1004':
Method 'Unprotect' of object '_Worksheet' failed

Being a reasonable guy, I went to look at the code, using (of course) Tools -> Macro -> Visual Basic Editor
But it won't let me look at the project's code, because it's password protected. I only get a "Project is Locked" "Project unviewable" error.

I can't remember for the life of me where it is I enter the password to view this stuff. Furthermore, if I'm back in Excel looking at the worksheet that didn't run its macros, the cells remain locked, as expected. I go to Tools -> Protection to unprotect the sheet and I see that the only option I have available is Protect Shared Workbook... Both Unprotect Sheet and Protect Workbook are disabled.

What's going on here? I remember the password for this silly thing, I just can't come up with a place to enter it!

Any help would be most appreciated.
0
Comment
Question by:BarryTice
9 Comments
 
LVL 11

Assisted Solution

by:mdmackillop
mdmackillop earned 160 total points
ID: 10778514
Hi BarryTice,
Try in the VB Editor, go to Tools/VBAProjectProperties

Regards
MD
0
 
LVL 7

Author Comment

by:BarryTice
ID: 10778932
I tried that, mdmackillip, and got the same error: Project is Locked / Project Unviewable.
0
 
LVL 3

Accepted Solution

by:
MandyC earned 180 total points
ID: 10782318
Go in to the VBA Project Explorer, click on the project and you will be prompted for the password (at least this is how it works in 97)
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
LVL 7

Author Comment

by:BarryTice
ID: 10782559
Thanks, MandyC. That's what I had expected to happen here, too. But when I click on the project in the VBA Project Explorer, I get the above pop-up. That's what's stumping me on this one.

-- b.r.t.
0
 
LVL 3

Expert Comment

by:MandyC
ID: 10782861
I suspect that this will fail like the other methods butyou cauld try right clicking on the Excel icon (top left) then selecting View code. This brings up the password box agian (This is 97 what version are you using.) :-(



0
 
LVL 7

Author Comment

by:BarryTice
ID: 10785334
MandyC --

Thanks for the suggestion, but that gives me the same error and then an "unspecified" error window, too (System error &H80004005. Unspecified error).

The Excel version is 2000.

I'm beginning to expect that this spreadsheet has just gotten corrupt and there's no way around it.

-- b.r.t.
0
 
LVL 26

Assisted Solution

by:Hilaire
Hilaire earned 160 total points
ID: 10811401
Create a new workbook,
create a new macro that loops through the currently opened workbooks,
checks workbook name until you find the workbook you want to unprotect
then use the
workbook.unprotect "YourPasswordGoesHere"
method on it

HTH

Hilaire
0
 
LVL 7

Author Comment

by:BarryTice
ID: 10843503
Sorry for the delay in getting back to this. I was out of town Friday through Tuesday, and am just now getting caught up.

Hilaire --

I've tried this code:
    Dim wkbk As Workbook
    For Each wkbk In Workbooks
        If InStr(wkbk.FullName, "Capture") <> 0 Then
            wkbk.UnProtect "Password"
        End If
    Next

but I'm still getting
Run-time error '1004':
Method 'Unprotect' of object '_Workbook' failed

Any other ideas, anyone? Thanks again!

-- b.r.t.
0
 
LVL 7

Author Comment

by:BarryTice
ID: 12127653
Well, it's six months later and I'm doing some cleanup.

None of these efforts ever got me into that workbook. I assume it was just a corrupt file somehow.

But I appreciate the efforts and have split the points accordingly.

Thanks for trying!

-- b.r.t.
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Here is why.
While repairing a PST file with ScanPST.exe, many users have encountered the error "Attempting to Validate BBT Refcounts". In this article, we are going to describe the reasons and solution to fix the error.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

595 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question