Can't unprotect sheet in Excel 2K

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.
LVL 7
BarryTiceAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
MandyCConnect With a Mentor Commented:
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
 
mdmackillopConnect With a Mentor Commented:
Hi BarryTice,
Try in the VB Editor, go to Tools/VBAProjectProperties

Regards
MD
0
 
BarryTiceAuthor Commented:
I tried that, mdmackillip, and got the same error: Project is Locked / Project Unviewable.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
BarryTiceAuthor Commented:
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
 
MandyCCommented:
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
 
BarryTiceAuthor Commented:
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
 
HilaireConnect With a Mentor Commented:
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
 
BarryTiceAuthor Commented:
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
 
BarryTiceAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.