Show file size dynamically (Excel 2007)

[Nathan Berton]

I'm using Excel 2007 and would like to show the active workbook's file
size somewhere on the UI, dynamically updated when the file is saved,
opened, activated, etc. I have a custom ribbon tab, but I don't think
you can show any sort of dynamic label on there, unless I"m mistaken.
I don't want it to be a button you click and have a msgbox pop up or
that sort of thing.

What method should I be pursuing here? I can't find any sort of way to
show this on the status bar, QAT, a custom ribbon tab, etc.

Thanks for your help!

 

[Peter T]

Just for ideas -

'' normal module
Function mySize() As String
Dim nSize As Long

nSize = FileLen(ThisWorkbook.FullName)
mySize = Format(nSize / 1024, "#,##0Kb")

End Function

Sub mySizeToSbar()
Application.StatusBar = "FileSize when last saved: " & mySize
End Sub


'' Thisworkbook module
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Application.OnTime Now, "mySizeToSbar"
End Sub

Private Sub Workbook_Deactivate()
Application.StatusBar = False
End Sub

Private Sub Workbook_Open()
mySizeToSbar
End Sub
'''' end THisworkbook

you could use this as a UDF in a cell -
=mySize()

Regards,
Peter T

 

[Nathan Berton]

This is great, thanks! However, is there some way to show the file
size without losing the rest of the status bar? That is, when I use
this function, I see the file size on the status bar, but I lose the
'Ready' status, the 'Calculate' button, and the macro recording
button. Is it one or the other, or is there some way to just tack the
file size on the end of the normal status bar?

Thanks again!

 

[Peter T]

There's only one section of the statusbar that can either display 'your'
custom info, as written by some VBA code, or Excel's (Ready, Calculate etc).
Can't have both at the same time.

Maybe simply reset the status bar if user changes a cell entry or even
selects a different cell, add to the existing code in the Thisworkbook
module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.StatusBar = False
End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
Application.StatusBar = False
End Sub

Don't include both the above

Of course there are other places to display the info, in a cell or a textbox
somewhere

Regards,
Peter T


This is great, thanks! However, is there some way to show the file
size without losing the rest of the status bar? That is, when I use
this function, I see the file size on the status bar, but I lose the
'Ready' status, the 'Calculate' button, and the macro recording
button. Is it one or the other, or is there some way to just tack the
file size on the end of the normal status bar?

Thanks again!

 

[Nathan Berton]

Peter,

I like the idea. I think I'll go with using the status bar and
resetting it after a selection change as you suggested, maybe
supplemented with a msgbox popup button. Thanks again for your help.