Why 'Unprotect' works for 2003 but not 2000?!

O

OrientalPearl

Hi all,

Maybe it's a kinda stupid question...but Im frustrated to get this
sorted!

Basically I created a workbook with sheets protected, which also
involves some macros behind. It works perfectly in Excel 2003, but it
fires run-time error 1004:

You cannot sue this command on a protected sheet. To unprotect the
sheet, use the Unprotect Sheet command...(rest omitted)

In debugging, the following line was focused:

Selection.Insert Shift :=xlDown

Apparently such an insertion attempt is not allowed(?). I have
unprotected the current sheet far at the beginning using:
masterworkbook.Sheets("Shipper").Unprotect Password:="MyPIN"

So can anyone tell why this does not work in Excel 2000(it does for
Excel 2003)? How can the insertion be done legitimately in Excel 2000
then?

Thanks in advance to any response!!

Regards
Frank
 
N

Norman Jones

Hi Frank,

With xl2k, in order to insert rows on a protected sheet there are two
options:

(1) Unprotect the sheet, insert the required row(s) and then reprotect
the sheet.

(2) Set the Protect method's UserInterfaceOnly parameter to true. This
enables VBA manipulation of the protected sheet, including the insertion of
rows.

However, this setting is not persistent and needs to be reset each time the
workbook is opened.

Perhaps, therefore, you could set protection in the Workbook_Open or
Auto_Open procedures, e.g.:

'=============>>
Sub Auto_Open()
With Worksheets("Sheet1")
.Protect Password:="drowssap", UserInterfaceOnly:=True
End With
End Sub
'<<=============

As you have discovered, protection functionality is increased in xl2003. If
an application is to be used with different versions of Excel, it is
necessary to restrict the functionality to that which is available with the
oldest version likely to be used.
 
D

Dave Peterson

I'd guess that that sheet was unprotected by your code. Maybe the password
changed????
 
O

OrientalPearl

Thanks for your response Norman!
The original protection (To make it simple, I didnot quote that the
protection was done by calling anther method which protects all sheets
in the workbook) does use the parameter 'userInterfaceOnly:=True'....I
noticed it's 'user...' not 'User...'. Is VBA case-sensitive? Probably
that's where it fails?? I have managed to get this problem solved by
placing 'ActiveSheet.Unprotect Password:="password"' just before where
it crashed and then protect the ActiveSheet again once the manipulation
is finished.
The other issue I discovered is that Excel 2003 is far more
flexible/tolerant...Whilst Excel 2003 is happy, Excel 2000 does not
like assigning Null/empty string (well some variables may well be Null
sometimes) value to another variable. So I have to add an If statement
to ALL assignment statement with the possibility of getting into this
awkward situation:

If xxx <> Null Then
yyy = xxx
End If

Thanks again Norman for your help!
Regards
Frank
 
O

OrientalPearl

Thanks Dave. That's a good point to check the password to make sure the
right one is used...In my case, the correct one is the only one being
used.

Regards
Frank
 
D

Dave Peterson

VBA is not case sensitive.

But you can fix the case by typing this:

dim UserInterfaceOnly
(and hit enter)

Then delete that line.

VBA isn't case sensitive, but it does have a memory.

In earlier versions, you could set the userinterfaceonly setting without the
password. In xl2002+, you need to provide the correct password. (Security was
beefed up (just slightly).)

But since you didn't mention userinterfaceonly in the original post, who'da
thunk that this could be the problem?

Could that be the problem?
 
O

OrientalPearl

My apologies for the original post not so informative for your
diagnosis.

Still not getting the point of typing 'dim UserInterfaceOnly<enter>'
and then deleting it straight away afterwards...maybe because I dont
understand how Excel's memory works
 
D

Dave Peterson

Excel remembers the upper/lower case of variables and keywords.

By using "Dim UserInterfaceOnly", you tell excel that you want it capitalized
that way.

But you don't actually want a variable named this. So you delete the line after
it fixes excel's memory about the case.

But the case of that keyword isn't important to VBA--it's only important to you.

Was not supplying the password the cause of your problem? (You didn't respond
to that portion.)
 
O

OrientalPearl

Sorry. The original code does use both UserInterfaceOnly and Password.
Password is required at all times during any interaction with both
certain locked cells and macro behind. Only one password is used in all
cases.
To be honest, I dont know what caused the problem(but adding the extra
'ActiveSheet.Unprotect Password:="blah"' just before the crashing point
does help and solve it) since even when the whole workbook is protected
by calling a dedicated method(it loops through all worksheets and lock
them up one by one setting UserInterfaceOnly True and Password), the
macro should still be able to work on it simply because
UserInterfaceOnly is True.

Many thanks for all your posts! May I also point you to my another
riddle on the following
link?http://groups.google.co.nz/group/mi...ramming/browse_thread/thread/1755f5981fcb06fe


Regards
Frank
 
D

Dave Peterson

If unprotecting the worksheet in that step works, then changing the protection
mode (to userinterfaceonly:=true) isn't working.

My guess is that you have some kind of code that hides the error (but I've been
wrong lots of times).

dim wks as worksheet
on error resume next
for each wks in activeworkbook.worksheets
wks.protect password:="blahh", userinterface:=true
next wks
on error goto 0

But that's just a guess.

If you want to try to resolve that problem, you may want to post the code that
changes that protection--and share when it's called.
 
O

OrientalPearl

That's the method doing the protection <in Utilities module>

'**************************************************************************
' Purpose: Protects all the worksheets in a workbook.
' Inputs: protectWorkbook: the workbook to protect
' Assumptions:
' Returns:
' Effects:
' Author: May
'**************************************************************************
Public Sub ProtectAllSheets(ByVal protectWorkbook As Workbook)
Dim intnumWorkbooks As Integer
For intnumWorkbooks = 1 To protectWorkbook.Worksheets.Count
protectWorkbook.Worksheets(intnumWorkbooks).Protect
userInterfaceOnly:=True, Password:="blah"
Next
End Sub

In another module, the above method is being called:
Utilities.ProtectAllSheets masterworkbook //sorry it is
'Protect' not 'Unprotect' as in my original post
< some code>
'some range has been selected'
Selection.insert //crashes here

As said, it should not prohibit macro from making changes as
userInterfaceOnly is set to True. One issue worth attention is that
when checking out from Excel interface(Tools>Protections>...), the main
worksheet(others are lookup sheet and temp import data sheet, both
hidden) and the entire workbook are protected....Is that possible that
the author of the original excel book also protected them using the
menu besides the above method, which might cause the problem?
It may not overkill to reemphasise that everything worked perfectly in
Excel 2003, but just not in Excel2k. So version may also be the key!

Thanks again!
 
D

Dave Peterson

When does that routine get run?

This is a setting that is not remembered when you close the workbook. It has to
be run when the workbook opens (or at least before you try to change the
worksheet with other code).

And there's no special protection scheme that I know about.
 
D

Dave Peterson

I see where you wrote that the protection is set right before you do the insert.

Are you sure you're protecting the correct worksheets?

If you move enough of your code to another test workbook, does it work there?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Macro can not unprotect a sheet in a shared workbook 4
Unprotect worksheet error 3
Object works in Excel 2000 but not in 2003 2
Simple VBA Code written in Excel 2003 not working in Excel 2000 4
Enable autofilter in protected worksheets in Excel 2000 6
Icons in Excel 2003 8
Works at one place but not another? 9
external data works in 2003 not in 2000 2

Top