Results 1 to 2 of 2
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Princeton, New Jersey, USA
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Sydney, Australia, New South Wales, Australia
    Posts
    251
    Thanks
    0
    Thanked 4 Times in 4 Posts

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

    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

    <pre>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
    </pre>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •