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

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

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

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.

---
Regards,
Norman


"OrientalPearl" wrote in message
oups.com...
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

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

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

OrientalPearl wrote:

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

--

Dave Peterson

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

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

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

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

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

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?

OrientalPearl wrote:

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

--

Dave Peterson

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

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

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

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.)

OrientalPearl wrote:

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

--

Dave Peterson

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

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/mic...55f5981fcb06fe


Regards
Frank

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

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.



OrientalPearl wrote:

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/mic...55f5981fcb06fe

Regards
Frank

--

Dave Peterson

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

Well, the routine(I assume you're referring to ProtectsAllSheets()...)
does not run by itself at all...As far as I see, it is called whenever
the original author reckons such a whole workbook protection is
required, though a robust protection, e.g. whenever the workbook is
opened, is desired.

May I also point out my another puzzle at:
http://groups.google.co.nz/group/mic...765cc21fe0c508

Thank you!

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

My apologies. The correct URL should be
http://groups.google.co.nz/group/mic...12516b47bf68a0

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

My apologies. The correct URL should be
http://groups.google.co.nz/group/mic...12516b47bf68a0

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

I don't have any more guesses.

OrientalPearl wrote:

My apologies. The correct URL should be
http://groups.google.co.nz/group/mic...12516b47bf68a0

--

Dave Peterson