Copy Status Bar Stat To Clipboard

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)) 
MyDataObj.PutInClipboard


end sub

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

sub copySum()


Range("U3")=Application.Sum(Selection)
Range("U3").copy


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
.PutInClipboard
End With
End If
Next

End Sub
[/vba]

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

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.

Rgds,
Jani

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
[/vba]