Excel 2007 formula calculation

[joeeng]

have ported great deal of vba code from Excel 2003 to Excel 2007. I am
happy to say that most things worked, even things I thought would not.
However, one issue has stumped me. I have some worksheets that have
slidebars to change some values which in turn feed into the lookup_value and
reference for Hlookup, Vlookup, and Offset functions, respectively. However,
with autocalculation set to manual, Vlookup, Hlookup, and Offset functions
are not recalculating when a me.calculate is issued via vba code in the
worksheet module. This code works perfectly in Excel 2003. Does anyone know
why these worksheet functions are not updating and what can be done to make
them work in Excel 2007?

 

[Jim Rech]

Assuming a "slidebar" is a scroll bar I don't see any change in behavior
between Excel 2003 and 2007. When a scroll bar from either the Forms or
Control Toolbar toolbars is used (you didn't say which you were using) and a
linked cell changes value, the sheet_change event is not fired. I assume
you're using the sheet change event. But the scroll bar change event is
fired for a Control Toolbox scroll bar. And a macro attached to a Forms
toolbar scroll bar would also fire and it could calc the sheet.

--
Jim
| have ported great deal of vba code from Excel 2003 to Excel 2007. I am
| happy to say that most things worked, even things I thought would not.
| However, one issue has stumped me. I have some worksheets that have
| slidebars to change some values which in turn feed into the lookup_value
and
| reference for Hlookup, Vlookup, and Offset functions, respectively.
However,
| with autocalculation set to manual, Vlookup, Hlookup, and Offset functions
| are not recalculating when a me.calculate is issued via vba code in the
| worksheet module. This code works perfectly in Excel 2003. Does anyone
know
| why these worksheet functions are not updating and what can be done to
make
| them work in Excel 2007?

 

[joeeng]

I am using the scrollbar control on a worksheet to change the data retrieval
values for the worksheet. In the Scrollbar.scroll procedure, I have code
which adds/deletes rows as needed, copies the cell formulas down to the new
rows, and then issues a me.calculate to recalculate the worksheet. This
me.calculate which worked in Excel 2003 is not working in Excel 2007 for
cells that contain Vlookup, Hlookup, and Offset functions in the formulas.
In further testing, I have found that activesheet.calculate and explicit
sheets("Sheet..").calculate does not work either. The only calculate method
that seems to be working for these cells is an explicit range(....).calculate.

 

[Jim Rech]

Weird. I've confirmed in a simple example that an Application.Calculate or
me.calculate does work when executed by Sub ScrollBar1_Change. But your
real case is much more complicated so something else must be going on.
Sorry I can't help.

--
Jim
|I am using the scrollbar control on a worksheet to change the data
retrieval
| values for the worksheet. In the Scrollbar.scroll procedure, I have code
| which adds/deletes rows as needed, copies the cell formulas down to the
new
| rows, and then issues a me.calculate to recalculate the worksheet. This
| me.calculate which worked in Excel 2003 is not working in Excel 2007 for
| cells that contain Vlookup, Hlookup, and Offset functions in the formulas.
| In further testing, I have found that activesheet.calculate and explicit
| sheets("Sheet..").calculate does not work either. The only calculate
method
| that seems to be working for these cells is an explicit
range(....).calculate.
|
|
| "Jim Rech" wrote:
|
| > Assuming a "slidebar" is a scroll bar I don't see any change in behavior
| > between Excel 2003 and 2007. When a scroll bar from either the Forms or
| > Control Toolbar toolbars is used (you didn't say which you were using)
and a
| > linked cell changes value, the sheet_change event is not fired. I
assume
| > you're using the sheet change event. But the scroll bar change event is
| > fired for a Control Toolbox scroll bar. And a macro attached to a Forms
| > toolbar scroll bar would also fire and it could calc the sheet.
| >
| > --
| > Jim
| > | > | have ported great deal of vba code from Excel 2003 to Excel 2007. I
am
| > | happy to say that most things worked, even things I thought would not.
| > | However, one issue has stumped me. I have some worksheets that have
| > | slidebars to change some values which in turn feed into the
lookup_value
| > and
| > | reference for Hlookup, Vlookup, and Offset functions, respectively.
| > However,
| > | with autocalculation set to manual, Vlookup, Hlookup, and Offset
functions
| > | are not recalculating when a me.calculate is issued via vba code in
the
| > | worksheet module. This code works perfectly in Excel 2003. Does
anyone
| > know
| > | why these worksheet functions are not updating and what can be done to
| > make
| > | them work in Excel 2007?
| >
| >
| >