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" 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

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

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

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