Hi everyone,

I encounter a problem with running macro in Excel. Basically all works
fine with Excel 2003(tested on 2 machines) whilst a run-time error
'1004' was fired on Excel 2000( failed on 2 machines). Apparently
there's somethiing wrong with compatibility issues between these 2
vesions. The detailed message says:

run-time error '1004':
The cell or chart you are trying to change is protected and therefore
read only.

That's true and used to prevent certain cells being modified by users.
But it does run smoothly on Excel 2003 without any problem.

The piece of code causing the problem:

Range("K10").Select
'Macro halts on the next line
ActiveCell.Formula = "=SUMIF($D$26:$D$65536,""POOL"",$M$26:$M$65536 )"

I need to make it work on both versions...Any idea of the solution? All
kinds of help will be appreciated.

Thanks
Frank

Hi Frank

Working for me in 2000 and 2003 with cell K10 unlocked (Ctrl-1)

--
Regards Ron de Bruin
http://www.rondebruin.nl


wrote in message oups.com...
Hi everyone,

I encounter a problem with running macro in Excel. Basically all works
fine with Excel 2003(tested on 2 machines) whilst a run-time error
'1004' was fired on Excel 2000( failed on 2 machines). Apparently
there's somethiing wrong with compatibility issues between these 2
vesions. The detailed message says:

run-time error '1004':
The cell or chart you are trying to change is protected and therefore
read only.

That's true and used to prevent certain cells being modified by users.
But it does run smoothly on Excel 2003 without any problem.

The piece of code causing the problem:

Range("K10").Select
'Macro halts on the next line
ActiveCell.Formula = "=SUMIF($D$26:$D$65536,""POOL"",$M$26:$M$65536 )"

I need to make it work on both versions...Any idea of the solution? All
kinds of help will be appreciated.

Thanks
Frank

thanks ron for your quick help. sorry i forgot to emphasize that the
cell k10 in this case does need to be locked at all times to prevent
user intervention. so is it possible to somehow make it work while the
cell is locked?

thanks again and regards
frank


--
uglyvb
------------------------------------------------------------------------
uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720
View this thread: http://www.excelforum.com/showthread...hreadid=519532

Activesheet.Protect UserInterfaceOnly:=True
Range("K10").Select
ActiveCell.Formula = "=SUMIF($D$26:$D$65536,""POOL"",$M$26:$M$65536 )"


--
Regards,
Tom Ogilvy


"uglyvb" wrote in
message ...

thanks ron for your quick help. sorry i forgot to emphasize that the
cell k10 in this case does need to be locked at all times to prevent
user intervention. so is it possible to somehow make it work while the
cell is locked?

thanks again and regards
frank


--
uglyvb
------------------------------------------------------------------------
uglyvb's Profile:
http://www.excelforum.com/member.php...o&userid=31720
View this thread: http://www.excelforum.com/showthread...hreadid=519532

Thank you very much Tom for your help on this regard. It works out just
perfectly...A nice alternative work-around from JakeyC on
http://www.excelforum.com/showthread.php?t=519542 also does the job.
Just wondering why I did not think of this option even without
knowledge of the proper one...maybe too focused on the version variance
rather then the error message itself...By the way, is it true that
version incompatibility was the original cause?

Many thanks and regards
Frank

P.S. Also my apologies for those who saw another similar thread
here...I posted on Google Group as well at the beginning and didn't
know the affiliation between Google and Excelforum, which resulted in
the replicate.


--
uglyvb
------------------------------------------------------------------------
uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720
View this thread: http://www.excelforum.com/showthread...hreadid=519532

Not compatibility per se (although loosely you could say it is
compatibility).

xl2002 and later offer many more options in terms of protection. xl2000
does not have those options so they are ignored. Apparently in xl2003 you
chose options that allowed the macro to operate but which are not supported
in xl2000. This is probably outside the code itself and is more an
environmental issue.

It is always best to develop on the lowest version which will be expected to
run the code. You have much fewer problems moving up rather than down.
Moving down almost guarantees you will have problems, expecially if you
record code such as for sorts or find where options present in xl2003 for
example are recorded and are unknown to xl2000. So the main error is
expecting xl2000 to operate with features only available in later versions.

--
Regards,
Tom Ogilvy


"uglyvb" wrote in
message ...

Thank you very much Tom for your help on this regard. It works out just
perfectly...A nice alternative work-around from JakeyC on
http://www.excelforum.com/showthread.php?t=519542 also does the job.
Just wondering why I did not think of this option even without
knowledge of the proper one...maybe too focused on the version variance
rather then the error message itself...By the way, is it true that
version incompatibility was the original cause?

Many thanks and regards
Frank

P.S. Also my apologies for those who saw another similar thread
here...I posted on Google Group as well at the beginning and didn't
know the affiliation between Google and Excelforum, which resulted in
the replicate.


--
uglyvb
------------------------------------------------------------------------
uglyvb's Profile:
http://www.excelforum.com/member.php...o&userid=31720
View this thread: http://www.excelforum.com/showthread...hreadid=519532

Thanks Tom for the detailed explanation on the right approach to
development. I know the proper way is to develop from the lower level
which should generally stay safe...just in my case it's the other way
around; the testers are using the old version somehow(Im just a new
comer)

Thanks again and regards
Frank


--
uglyvb
------------------------------------------------------------------------
uglyvb's Profile: http://www.excelforum.com/member.php...o&userid=31720
View this thread: http://www.excelforum.com/showthread...hreadid=519532