Solved

Can't unprotect sheet in Excel 2K

Posted on 2004-04-07
9
2,852 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
RoboForm Secure Password Management System

RoboForm Everywhere - Superb Browser Support
Windows / Apple / IOS / Android / Linux / Chrome OS
Use different complex passwords everywhere
Best Secure Password Management by far
Synchronize all of your devices instantly
Safe, Secure & Highly Recommended!

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

734 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