Version problem?

[[email protected]]

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

[Ron de Bruin]

Hi Frank

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

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


<[email protected]> wrote in message news:[email protected]...
> 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
>

[uglyvb]

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

[Tom Ogilvy]

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


--
Regards,
Tom Ogilvy


"uglyvb" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[uglyvb]

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.

[Tom Ogilvy]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[uglyvb]

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