Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show file size dynamically (Excel 2007)
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show file size dynamically (Excel 2007)
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" wrote in message ... 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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show file size dynamically (Excel 2007)
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! On Jun 16, 4:38*am, "Peter T" <peter_t@discussions wrote: 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" wrote in message ... 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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show file size dynamically (Excel 2007)
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 "Nathan Berton" wrote in message ... 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! On Jun 16, 4:38 am, "Peter T" <peter_t@discussions wrote: 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" wrote in message ... 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Show file size dynamically (Excel 2007)
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. On Jun 16, 10:45*am, "Peter T" <peter_t@discussions wrote: 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 "Nathan Berton" wrote in message ... 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! On Jun 16, 4:38 am, "Peter T" <peter_t@discussions wrote: 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" wrote in message .... 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Managing File Size in Excel 2007 | New Users to Excel | |||
Excel 2007 File Size | Excel Discussion (Misc queries) | |||
How to reduce file size when converting to pdf for excel 2007? | Excel Discussion (Misc queries) | |||
Unable to open excel file and when view the file size show as 1 KB | Excel Discussion (Misc queries) | |||
Function To Dynamically Display File Size | Excel Worksheet Functions |