Copy Status Bar Stat To Clipboard

  • Hi All,

    I want to create a macro that will copy the stat that is displayed in the status bar (bottom right corner - e.g. Sum, Count, Max, Min, etc.) into the clipboard in order to paste it to another workbook or worksheet.

    At present, I'm creating a formula on the active sheet and pasting the value to the detination sheet/workbook. Since I have to do this many times over, I'd prefer to have a macro to copy the value in the status bar to the clipboard, and simply pasting.



  • Re: Copy Status Bar Stat To Clipboard

    If you use the sum function all time this will work.

    sub putSumOnClipBoard()
    Dim MyDataObj As New DataObject 
    MyDataObj.SetText Format(Application.Sum(Selection)) 
    end sub

    but it might be formatted as text after pasting. If you have a spare cell you could use

    sub copySum()
    end sub
  • Re: Copy Status Bar Stat To Clipboard

    Try this.

    You can not directly access the information but you can determine which function to use on the selected range.

    [vba]Sub Macro1()
    ' Include reference to MSForms
    Dim cbrCnt As CommandBarControl
    Dim vntValue As Variant
    Dim objDO As DataObject

    If TypeName(Selection) <> "Range" Then Exit Sub

    For Each cbrCnt In Application.CommandBars("AutoCalculate").Controls
    If cbrCnt.State = msoButtonDown Then
    Select Case UCase(Replace(Replace(cbrCnt.Caption, "&", ""), " ", ""))
    Case "AVERAGE"
    vntValue = Application.WorksheetFunction.Average(Selection)
    Case "COUNT"
    vntValue = Application.WorksheetFunction.CountA(Selection)
    Case "COUNTNUMS"
    vntValue = Application.WorksheetFunction.Count(Selection)
    Case "MAX"
    vntValue = Application.WorksheetFunction.Max(Selection)
    Case "MIN"
    vntValue = Application.WorksheetFunction.Min(Selection)
    Case "SUM"
    vntValue = Application.WorksheetFunction.Sum(Selection)
    Case Else
    Exit For
    End Select
    MsgBox vntValue
    Set objDO = New DataObject
    With objDO
    .SetText CStr(vntValue), 1
    End With
    End If

    End Sub


  • Re: Copy Status Bar Stat To Clipboard

    I'm getting the following error on both the macros:

    "Compile error: User-defined type not defined".

    Is there a special add-in that I need to include for the code to work?


  • Re: Copy Status Bar Stat To Clipboard

    You need to reference the Microsoft Forms Object Library. Simply in VBE go to Tools > References... > Microsoft Forms 2.0 Object Library and Bob's your uncle.

  • Re: Copy Status Bar Stat To Clipboard

    Fenclif: Thanks for the help. The macros are working now. (I don't have an uncle Bob - but if you insist ...)

    Andy: Thanks. Your code did the trick.

    Poplen: Your code works great for sums.

    One last question: Is there a way to programmatically include the MS Forms 2.0 Reference?



  • Re: Copy Status Bar Stat To Clipboard

    Quote from mhabib

    One last question: Is there a way to programmatically include the MS Forms 2.0 Reference?

    This is what I use. I am not actually sure whether the GUID number is the same from Excel version to another.

    sFormsGUID = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}"
     ThisWorkbook.VBProject.References.AddFromGuid GUID:=sFormsGUID, Major:=1, Minor:=0

    I know I have the code to find out the GUID name for all the references in a project on my work computer, I can post it tomorrow if this does not work.


  • Re: Copy Status Bar Stat To Clipboard

    You have code something like this Jani?

    Enter this in the immediate window in order to list references in active project.
    [vba]for i=1 to 5:?application.VBE.ActiveVBProject.References(i).Description ,application.VBE.ActiveVBProject.References(i).Guid:next i


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!