Recalcing formulas in a worksheet in Excel

[rossmcl]

Hi

I have a spreadsheet with a few VBA-driven formulas.

When I manually recalculate the cells with the forumulas in them (by placing my cursor at the end of the formula and hitting Return, it all works fine.

But when I add a button to the worksheet, and try to recalculate that way, I cannot get it to work.

I tried using the below as the code behind my button, but it isnt working.

(OrderNumberCreator is the name of the worksheet)


Private Sub cmdCalc_Click()

Sheets("OrderNumberCreator").Calculate

End Sub



Please help, this is driving me crazy!
RM

 

[Loomah]

Hi
I can't recreate your problem but have you tried
cells.calculate
or just calculate on it's own?

Happy Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

Hi rossmcl,

Try adding [blue]Application.Volatile[/blue] to your VBA Functions which are used in the Cells. This will force them to be recalculated whenever calculation is done on the worksheet; without it, it will only be done in very limited circumstances.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[Loomah]

And, yes, I should read posts more thoroughly before replying!! For some reason I read it as inserting formula via VBA.
To drink or not to drink. It is a Friday
;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[TonyJollans]

To drink, definitely to drink

Not only Friday, but Bank Holiday weekend.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.