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

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" <CatchFrank@gmail.com> wrote in message
news:1143693898.061273.182300@t31g2000cwb.googlegroups.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
>

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

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

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

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

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

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

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

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

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!

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.

OrientalPearl wrote:
>
> 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!

--

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?



OrientalPearl wrote:
>
> 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!

--

Dave Peterson

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!

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

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

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