Office 97 Code Edited in 2000 Will Not Run

[Craig Phillips]

Ok, well I've change the line to read

Sheet2.Range("A37:L50").Borders.LineStyle = xlLineStyleNone

and it has had no effect. Anymore ideas except drinking coffee :eek

 

[Dave Peterson]

Your post is separated from the original thread--so it's difficult to know what
the problem is.

 

[Craig Phillips]

The prob,lem I'm having, is that the code appears to be confused an
looking for Office 2000 objects when run under 97. The code wa
originally written in 97 and edited using 2000. I have attempted t
save the file as an earlier version and even just cut and paste cod
into it under 97. When it it run, most references to any excel objec
type methods tend to fail with run-time error 1004.
This includes the line that I included earlier as well as copy etc
:confused

 

[Dave Peterson]

There's a bug in xl97 that concerns macros called from controls placed on a
worksheet (from the control toolbox toolbar).

If that's the case with you (like a commandbutton), you can change the control's
takefocusonclick property to false.

Or add:

activecell.activate

to the top of your procedure.

If that's not it, you may want to post the portion of the code that's blowing up
and how it's initiated.

 

[Craig Phillips]

Function SetupCurrentTemplate() As Boolean
Dim row As Integer
Dim column As Integer
Dim write_row As Integer
Dim write_column As Integer
Dim levelCount As Integer
Dim ReadDepthVal As Range
Dim ClearVal As Range
Dim WriteDepthVal As Range
Dim C As Object

'SETUP RANGES USED TO READ AND WRITE DATA
Set ReadDepthVal = Cells(4, 1) 'START OF CURRENT DEPTHS TO REA
FOR 3D MODEL

Set ClearVal = Range("A37:L50") 'RANGE TO CLEAR ON TEMPLATE FORM
BEFORE UPDATE
Set WriteDepthVal = Cells(37, 1) 'START OF CURRENT DEPTHS TO WRIT
FOR 3D MODEL

column = 1
row = 4
write_row = 37
write_column = 1
levelCount = 0


'CLEAR TEMPLATE
Sheet2.Range("A37:L50").Value = ""
Sheet2.Range("A37:L50").Borders.LineStyle = xlLineStyleNone


this is as far as the code get before it falls over. This is only a
example though, there are other areas of my project which has problem
as well. All of the problems though, have only occured after th
project was edited under Office 2000, then run under 97.... VER
FRUSTRATING

All the code is run behind the scenes and not activated using a comman
button, except at the very start which is o

 

[Dave Peterson]

I'm not quite sure what this means:

All the code is run behind the scenes and not activated using a command
button, except at the very start which is ok

If you're starting from a control placed on a worksheet from the control toolbox
toolbar, see the previous suggestion.

If you're starting from a worksheet formula in a cell, then you can't do this
kind of thing.

Sheet2.Range("A37:L50").Value = ""

You can only return a value to the cell(s) that hold the formula.

 

[Craig Phillips]

When I say run from behind the scenes, I mean the user has n
interaction once they press the run button. The code is part of
complex set of functions which is used by oceanographers to determin
various maximum returns for waves, current etc over periods rangin
from 1 to 10000 years.

The user enters a few required criteria data and presses a "run
button. The rest of the code is called in various dependent function
and procedures, which in turn produce the resultant excel spreadsheet
containing the data.


All of the code will run fine under office 2000, however! the proble
arises when it is run under 97. Even though it was produced under 9
with only a few minor tweaks performed from within 2000

 

[Dave Peterson]

What kind of button and where is it located?

Is it on a userform?

Is it on a worksheet? If it's on a worksheet, is it from the Forms toolbar or
from the control toolbox toolbar?

Again, if it's from a commandbutton placed directly on the worksheet, you may
want to set the .takefocusonclick property to false. (or add
"activecell.activate" near the top of your code.)