Displaying column width and row height

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!

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







In article <[email protected]>,
"swirlygirl" <[email protected]> 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!

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").Caption = _
> Format(Target.Height / nPPI, "0.00\""")
> CommandBars.FindControl(Tag:="jem_column_width").Caption = _
> Format(Target.Width / nPPI, "0.00\""")
> End Sub
>
>
>
>
>
>
>
> In article <[email protected]>,
> "swirlygirl" <[email protected]> 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!
>

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"
<[email protected]> 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.