Displaying column width and row height

[Guest]

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!

 

[JE McGimpsey]

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").Caption = _
Format(Target.Height / nPPI, "0.00\""")
CommandBars.FindControl(Tag:="jem_column_width").Caption = _
Format(Target.Width / nPPI, "0.00\""")
End Sub

 

[Guest]

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.

 

[Gord Dibben]

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