Results 1 to 6 of 6

Thread: AutoAvg

  1. #1
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    AutoAvg

    There is a handy toolbar icon: AutoSum, that allows us to highlight a range and quickly insert the +SUM function. Anyone ever tried to create a button that would "AutoAverage"? How handy that would be, for me anyway!
    - Ricky

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoAvg

    ** GeoffW. Long line split ***

    Ricky,

    Here is some code that adds a toolbarbutton next to the AutoSum button on the commandbar(3). Pressing that button calculates the average of all cells above the activecell. Customize the code for your specific case, if you want.

    <pre>Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteToolBarButton
    End Sub

    Private Sub Workbook_Open()
    DeleteToolBarButton
    AddToolBarButton
    End Sub

    </pre>


    The code above adds a button when the workbook is opened and deletes it when the workbook is closed.

    <pre>Sub AddToolBarButton()
    Dim NewButton As CommandBarButton
    Dim AutoSumIndex As Integer
    On Error Resume Next
    AutoSumIndex = Application.CommandBars(3).Controls("AutoSum").Ind ex
    Set NewButton = Application.CommandBars(3).Controls.Add _
    (Type:=msoControlButton, before:=AutoSumIndex)
    With NewButton
    .FaceId = 348
    .OnAction = "AutoAvg"
    .Caption = "AutoAvg"
    End With
    End Sub

    Sub DeleteToolBarButton()
    On Error Resume Next
    Application.CommandBars(3).Controls("AutoAvg").Del ete
    End Sub

    Sub AutoAvg()
    Dim R As Range
    On Error Resume Next
    Set R = Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 0).End(xlUp))
    If Err.Number = 0 Then
    ActiveCell.Formula = "=AVERAGE(" & R.Address & ")"
    R.Select
    Else
    Err.Clear
    MsgBox "No range available for input"
    End If
    End Sub

    </pre>


    These subroutines should be placed in a general module of the VBAproject of the workbook.
    The deletetoolbarbutton and addtoolbarbutton can be set private but then you have to place them in the ThisWorkbook module.
    You can make an add-in of this workbook to have the AutoAvg button permanently available.

  3. #3
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoAvg

    Hans, I certainly appreciate the response. I have removed the AutoSum toolbar button and used the customize routine to add that function to the |Insert| menu. Do you think that could be achieved with the AutoAvg feature as well?
    - Ricky

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoAvg

    You can attach the subroutine AutoAvg to whatever button you want. Just delete the code in the ThisWorkbook module.

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoAvg

    Thanks HP, I've got it functioning correctly and I like it.
    - Ricky

  6. #6
    Star Lounger
    Join Date
    Jan 2001
    Location
    Hemel and/or Luton, UK, Hertfordshire, England
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: AutoAvg

    Since no-one mentioned it, I will just let you know that in the new version of Excel (Excel 2002 in Office XP) the AutoSum button has been really jazzed up. It now has one of those little tiny chooser arrows next to it, and when you hit the arow you get the choice of

    SUM
    AVERAGE
    COUNT
    MAX
    MIN

    all of which pop into the cell and automatically set -up the range as AutoSum would.

    I say well done MS <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> for this improvement!

Posting Permissions

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