Adding STDEV to the AutoCalculate Area (Excel l2000 or 2003)

Topic

New Reply

Hi All,

After a training class this week where I introduced the AutoCalculate, I was asked if STDEV can be added as a function when you right-click.
I said “probably” …

I found a post that provided this code:

Sub test()
Dim cControl As CommandBarControl
With Application.CommandBars(“AutoCalculate”)
For Each cControl In .Controls
cControl.Delete
Next
Set cControl = .Controls.Add(msoControlButton, , , , True)
cControl.Caption = “STDEV”
End With
End Sub

Sub ResetCommandbar()
Application.CommandBars(“AutoCalculate”).Reset
End Sub

However, all it produced was text and it did not act as a function.

This is a “nice to know” but, not critical for me. But, if it’s not a bother, … any help is appreciated.
–cat

Reply | Quote

Replies

This isn’t the whole solution, but it’s moving in the right direction. See the .OnAction property to launch the macro.
I’m still trying to find out how to set that part of the status bar. Anyone else know?
Peter

Sub test()
Dim cControl As CommandBarControl
With Application.CommandBars("AutoCalculate")
For Each cControl In .Controls
cControl.Delete
Next
Set cControl = .Controls.Add(msoControlButton, , , , True)
cControl.Caption = "STDEV"
cControl.OnAction = "StdDevIt"
End With
End Sub

Sub ResetCommandbar()
Application.CommandBars("AutoCalculate").Reset
End Sub

End With
End Sub

Private Sub StdDevIt()
On Error Resume Next
Dim val
val = Application.StDev(ActiveWindow.RangeSelection)
MsgBox "StdDevOfit = " & val
End Sub

Reply | Quote