Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Is there a way to display the column width and row height of a cell on the
toolbar somewhere, similar to the way it shows the column and row number? I've looked at the CELL function which has the column width, but not the row height. Plus if it changes you have to hit F9 to refresh. It would be nice to see both when you select a cell. Thanks! |
#2
![]() |
|||
|
|||
![]()
One way:
I've posted an add-in to my ftp site that contains the code below, which I pulled out of a larger routine: ftp://ftp.mcgimpsey.com/excel/width_addin.xla It creates a new toolbar that displays the height and width of the selection in inches. It doesn't update when the column width or row height are changed (there's no event that fires when the change is made), but the next selection will cause the update. Note that error checking is non-existent. Note that XL04's Page Layout view automatically displays row heights and column widths in inches or centimeters: Put this in the ThisWorkbook code module: Dim wbc As Width_Bar_Class Private Sub Workbook_Open() Set wbc = New Width_Bar_Class End Sub Put this in a new Class module (VBE: Insert/Class Module) named Width_Bar_Class: Public WithEvents oApp As Application #If Mac Then Const nPPI = 72 #Else Const nPPI = 96 #End If Private Sub Class_Initialize() Set oApp = Application On Error Resume Next CommandBars("My Width Bar").Delete On Error GoTo 0 With CommandBars.Add(Name:="My Width Bar", Temporary:=True) With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "Height: " End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "0.00""" .Tag = "jem_row_height" End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "Width: " End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "0.00""" .Tag = "jem_column_width" End With .Visible = True End With End Sub Private Sub Class_Terminate() On Error Resume Next CommandBars("My Width Bar").Delete On Error GoTo 0 End Sub Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) CommandBars.FindControl(Tag:="jem_row_height").Cap tion = _ Format(Target.Height / nPPI, "0.00\""") CommandBars.FindControl(Tag:="jem_column_width").C aption = _ Format(Target.Width / nPPI, "0.00\""") End Sub In article , "swirlygirl" wrote: Is there a way to display the column width and row height of a cell on the toolbar somewhere, similar to the way it shows the column and row number? I've looked at the CELL function which has the column width, but not the row height. Plus if it changes you have to hit F9 to refresh. It would be nice to see both when you select a cell. Thanks! |
#3
![]() |
|||
|
|||
![]()
Thank you. This worked for my Office 2003 version, but I have a user on
Office 2000 that gives me an error. I'm not real familar with Visual Basic, so I'm waiting for him to upgrade to 2003. He says he thinks that older versions of Excel had this information in the bottom right status bar, but I don't know or remember. "JE McGimpsey" wrote: One way: I've posted an add-in to my ftp site that contains the code below, which I pulled out of a larger routine: ftp://ftp.mcgimpsey.com/excel/width_addin.xla It creates a new toolbar that displays the height and width of the selection in inches. It doesn't update when the column width or row height are changed (there's no event that fires when the change is made), but the next selection will cause the update. Note that error checking is non-existent. Note that XL04's Page Layout view automatically displays row heights and column widths in inches or centimeters: Put this in the ThisWorkbook code module: Dim wbc As Width_Bar_Class Private Sub Workbook_Open() Set wbc = New Width_Bar_Class End Sub Put this in a new Class module (VBE: Insert/Class Module) named Width_Bar_Class: Public WithEvents oApp As Application #If Mac Then Const nPPI = 72 #Else Const nPPI = 96 #End If Private Sub Class_Initialize() Set oApp = Application On Error Resume Next CommandBars("My Width Bar").Delete On Error GoTo 0 With CommandBars.Add(Name:="My Width Bar", Temporary:=True) With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "Height: " End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "0.00""" .Tag = "jem_row_height" End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "Width: " End With With .Controls.Add(Type:=msoControlButton) .Style = msoButtonCaption .Caption = "0.00""" .Tag = "jem_column_width" End With .Visible = True End With End Sub Private Sub Class_Terminate() On Error Resume Next CommandBars("My Width Bar").Delete On Error GoTo 0 End Sub Private Sub oApp_SheetSelectionChange(ByVal Sh As Object, _ ByVal Target As Excel.Range) CommandBars.FindControl(Tag:="jem_row_height").Cap tion = _ Format(Target.Height / nPPI, "0.00\""") CommandBars.FindControl(Tag:="jem_column_width").C aption = _ Format(Target.Width / nPPI, "0.00\""") End Sub In article , "swirlygirl" wrote: Is there a way to display the column width and row height of a cell on the toolbar somewhere, similar to the way it shows the column and row number? I've looked at the CELL function which has the column width, but not the row height. Plus if it changes you have to hit F9 to refresh. It would be nice to see both when you select a cell. Thanks! |
#4
![]() |
|||
|
|||
![]()
Excel 97......
Left-click on the horizontal scrollbar and Excel will show which Column is currently positioned at left of window. That's all it shows. No sizes. Perhaps this is what he remembers. Not supported in 2002 or 2003 versions. Gord Dibben Excel MVP On Mon, 7 Feb 2005 12:53:02 -0800, "swirlygirl" wrote: He says he thinks that older versions of Excel had this information in the bottom right status bar, but I don't know or remember. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|