Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can't unprotect sheet in Excel 2K

Posted on 2004-04-07
9
Medium Priority
?
2,859 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Industry Leaders: 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

This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …

721 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