How to press button with vba code

Question

Hi

I have added toggle button in my addin. Now I want to check if a value is true when I open a workbook and if it is true I want the button to be pressed. But I can not figure out how to press the button from vba. Any suggestions?

Im using excel 2010

/Lina

Answer 1

A few side comments:

1. The status of recalculation is displayed at the bottom left of the file on the status bar.  If calculation needs to be done the messages is "Calculation" if not there is no message.
2. You can add the Manual or Automatic buttons to the QAT by choosing Formulas, Calculation Options, and right clicking either Manual or Automatic or both and choosing Add to Quick Access Bar.  If you add Automatic, if the workbook opens in Manual mode the Automatic checkbox will be unchecked.  They can check it to activate Automatic calculation.  If they want to go both ways you can put both options on the QAT.
3. You can add a msgbox to the thisWorkbook_Open event that tells the user the calculation state.  Or you can get fancier and give them a userform with more options - for example you could give them Manual, Automatic checkboxes or toggle buttons and a button to calculate now.

---

If this answer solves your problem, please check, Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers Shane Devenshire

Answer 2

Hi

Thanks for pointing me in right direction :). Don't know what I was thinking about last friday. Now I added the built in calculation buttons on our customized ribbon.

:)

Answer 3

The users on my workplace wants to have a simple way to see if manual calculation is on or off, the think that the button on Formulas ribbon is to far away and want their own on the cusotom ribbon I've made for them before.

They want the button to be highlighted if manual calculation is on

I have added a toggleButton in my custom ribbon

<togglebutton id="btnToggleCalc" imageMso="Calculator" size ="large" onAction="ToggleCalc"/>

The button itself works fine when I use it on a open document it toggles between automatic calculation and manual calculation. But I would like it to show correct state when a document is opened and when someone changes Calculation options with the built in button.

I thought that if I check the application.calculation in Workbook_open I could just toggle my button with code. But I can not find a way of clicking a button with vba.

I tried to make a iRibbonControl object but I dont know how to set it to be my "btnToggleCalc"

Or is there a better way off doing it?

I also wonder if there is an event fired when a person changes Calculation mode?

Answer 4

Check out help on the Run method

Application.Run()

Application.Run "MYCUSTOM.XLM!My_Func_Sum"

Answer 5

Hi,

I'm not clear about what you mean by 'Toggle button' but whatever that object is you wouldn't '*physically'*press it you would read it's status(value) and if required change the status or alter the value with code, like this maybe

MyVal = Sheets("Sheet1").OptionButton1.Value

Sheets("Sheet1").OptionButton1.Value = True

---

If this post answers your question, please mark it as the Answer.

Mike H