Application.Calculation gives run time error

[Martien Janssen]

Hallo,

In one of my VB subroutines I have included an Application.Calculation =
xlCalculationManual instruction. I normally activate this subroutine by
attaching it as a macro (Assign Macro) to a button which I have added to my
worksheet using the Forms Toolbar. This works without problems.

Now I would like to use the Control Toolbox to put a similar button on my
worksheet (I am using Excel 97). However, to this button I can not directly
assign a macro anymore (at least I don't know how), so I use the
Button_x_Click event to start my subroutine. I have added this code to my VB
sheet:

Private Sub Button_X_Click()
Call Y
End Sub

Subroutine Y is placed in a module sheet within the same workbook

Sub Y
Application.Calculation = xlCalculationManual
.. rest of code
End Sub

When I now click on the button, I get a runtime error 1004 "Method
calculation of object application failed"

Any reaction is appreciated,
Martien

 

[Dave Peterson]

I'm not sure if this will help, but you could try:
xlManual
Both constants represent -4135

(but maybe xlCalculationManual was added with xl97???)


And another guess (your code worked ok for me in xl2003)...

There is a bug in xl97 with controls from the control toolbox toolbar. You can
go into design mode and change the .takefocusonclick to false.

This bug was fixed in xl2k.

If the control doesn't have this property, you can add a line to your code:

activecell.activate

(which will actually work even if the control has a .takefocusonclick property)

This is a common problem when a range is being manipulated--I'm not sure if it
helps with the application.calculation line, though.

Could you post back your results (I'm kind of curious).

 

[Peter T]

Hi Martien,

In Designer mode (top left button on the controls toolbox menu) right click
the button, view properties.
Change the TakeFocusOnClick" property to false (only need to do this in
XL97).

You should now be able to run your macro or move that calculation line into
the Click event.

Macros are not assigned to Worksheet controls from the Controls toolbox
menu. Instead they respond to Events, of which "Click" is just one of many.
Being the most common event for a button that is what automatically gets
written into the sheet module. With the cursor in the click event look at
the dropdown top right of the module.

Regards,
Peter T

 

[Peter T]

Hi Dave,

I didn't see yours when I sent mine. You asked the OP -

Could you post back your results (I'm kind of curious).

In my XL97 I replicated the OP's error. Changing .takefocusonclick to false
fixed it

Regards,
Peter T

 

[Martien Janssen]

Dear Peter and Dave,

Many thanks for your reply. Indeed, setting .takefocusonclick to false did
the job. I would never have found that one myself.

Many thanks again,

Regards,

Martien

 

[Dave Peterson]

Thanks for checking (and posting!).

Hi Dave,

I didn't see yours when I sent mine. You asked the OP -


In my XL97 I replicated the OP's error. Changing .takefocusonclick to false
fixed it

Regards,
Peter T

 

[Dave Peterson]

And thanks for posting back with your results.

Dear Peter and Dave,

Many thanks for your reply. Indeed, setting .takefocusonclick to false did
the job. I would never have found that one myself.

Many thanks again,

Regards,

Martien