Solved

Can't unprotect sheet in Excel 2K

Posted on 2004-04-07
9
2,847 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 40 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 45 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 40 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

825 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