Custom button on menu toolbar

[JackDanIce]

Hi,

Excel version: 2003

I have a custom button on my toolbar that switches calculation of the sheet between automatic and manual. The image I've used is of a calculator.

Is it possible to have this button change it's colour depending on which status calculation the sheet is? E.g. green if automatic, red if manual. Image doesn't have to be the calculator, could just be a circle and the colour fill of it changes.

The code attached to the button is:

Sub ToggleCalc()

With Application
    If .Calculation = xlCalculationAutomatic Then
        .Calculation = xlCalculationManual
    Else: .Calculation = xlCalculationAutomatic
    End If
End With

End Sub

Thanks,
Jack

 

[RoryA]

You'd need separate images and the code would have to load them based on the setting. Given that you can alter the setting through the options too, how do you intend to ensure it's in sync?

 

[Jaafar Tribak]

Hi,

Excel version: 2003

I have a custom button on my toolbar that switches calculation of the sheet between automatic and manual. The image I've used is of a calculator.

Is it possible to have this button change it's colour depending on which status calculation the sheet is? E.g. green if automatic, red if manual. Image doesn't have to be the calculator, could just be a circle and the colour fill of it changes.

The code attached to the button is:

Sub ToggleCalc()

With Application
    If .Calculation = xlCalculationAutomatic Then
        .Calculation = xlCalculationManual
    Else: .Calculation = xlCalculationAutomatic
    End If
End With

End Sub

Thanks,
Jack

Toggle the State Property of the CommandbarButton Control object between msoButtonDown and msoButtonUp

 

[JackDanIce]

Good point Rory, hadn't thought about the sync part. However, since it's only me that's using it, I'm only going to use the button than via Tools-Options.

Jaafar, thank you, will look into it

 

[AlphaFrog]

Sub ToggleCalc()

    Dim cbcCalcMode As CommandBarControl
    
    With Application
        Set cbcCalcMode = .CommandBars(.Caller(2)).Controls(.Caller(1))
        
        If .Calculation = xlCalculationAutomatic Then
            .Calculation = xlCalculationManual
            cbcCalcMode.FaceId = 3785   [COLOR="Green"]' "F9" icon[/COLOR]
            cbcCalcMode.TooltipText = "Manual Calculation: ON"
        Else
            .Calculation = xlCalculationAutomatic
            cbcCalcMode.FaceId = 283    [COLOR="Green"]'Calculator icon[/COLOR]
            cbcCalcMode.TooltipText = "Automatic Calculation: ON"
        End If
        
    End With

End Sub

Built-In Excel Icon ID Map
(Web site says it's for Outlook but it's the same for Excel)

 

[JackDanIce]

Hi Alpha,

Apologies for late reply. In your code, this line is throwing an error:

Set cbcCalcMode = .CommandBars(.Caller(2)).Controls(.Caller(1))

Of Run-time error '13': Type mismatch

Any suggestions?

Jaffar, I'm using Excel 2003. I have changed this now to a command button on the worksheet itself but there doesn't appear to be a state property for this. Any suggestions? Even a colour change when the IF statement is tested would be fine.

Thank you all,
Jack

 

[AlphaFrog]

this line is throwing an error:

Set cbcCalcMode = .CommandBars(.Caller(2)).Controls(.Caller(1))

...I have changed this now to a command button on the worksheet

I think you've answered your own question. The line of code that errors is expecting a toolbar button and not a command button on the worksheet.

The whole point of the code I provided was to toggle the icon of a toolbar button.

 

[RoryA]

FYI, if you want a reference to the commandbarcontrol that was clicked, you can use Application.Commandbars.Actioncontrol

 

[JackDanIce]

Hi Alpha,

Apologies, oversight and school boy error for not working that out. Busy at work and trying to deal with several things at the same time. I'll put it down to my lack of multi-tasking abilities!

Thanks for that too Rory, let me try a few things here and hopefully get this to work.

Best,
Jack