ProcessTrends.Com
Home
Chart Gallery
Video Tutorials
Trend Analysis
Using R
Data Visualization
 Global Warming
Map Excel Data with Google Earth
Chart Doctor
Advanced Chart Techniques
Links
What's New
Downloads
  About      
 Search Site RSS
   04/4/2008 4:30 PM

 

        Adjusting Cell Size

This tutorial shows you how to adjust the size a cell's width and height in points.

The Chrt_in_cell.xls file gives an example of how to do set cell size in points and how to make a chart in a cell with VBA.        

1. Excel Measurement Units

In an Excel worksheet, we can adjust the width of a column by dragging the boundary heading to the desired width. A ScreenTip displays the width in both number of standard font characters and pixels. In the example to the lower left, Column M is 8.6 standard font characters wide, equivalent to 48 pixels.  We can also control the row height by dragging the row boundary to desired height. A ScreenTip shows the height in points and pixels. In the example to the lower right, Row 49 height is 20.25 points, 27 pixels.

 2. Converting Between Characters, Pixels and Points

A point is 1/72nd of an inch. A pixel is 0.75 points. We can easily convert between points, pixels and inches, as shown in the following table.     

Points Pixels Inches
18 24 0.25
36 48 0.5
72 96 1.00
108 144 1.5
144 192 2.0

Converting between points, pixels and inches is pretty simple. How do we handle column widths?  We can convert pixels to column width in characters with the formula:

Column Width (Characters)  = (Width  Pixels - 5)/5

3.  Setting Cell Width and Height with VBA

The VBA code below  lets you input the desired cell width and height  in points and then resizes the row and columns to the requested size of the active cell.

 

Public Sub adjust_cell_size()
' | Procedure to adjust active cell size to user's width and height requirements
' | D. Kelly O'Day, ProcessTrends.Com
' | 2/15/06

  Dim cell_add As String
   On Error Resume Next                                                 '  In case user cancels
' Determine target cell
  cell_add = ActiveCell.Address
' Ask user for cell size in points: width & height
   wide_pts = InputBox("Cell: " & cell_add & " Enter target width - points", "Target Width - Points ")
   height_pts = InputBox("Cell: " & cell_add & " Enter target height - points", "Target Height - Points")
' Calculate width in Pixels
     wide_pix = wide_pts * 1.3333333333333
' Calculate width in standard font characters
    wide_char = (wide_pix - 5) / 5
' Establish row and column
    what_row = ActiveCell.Row
    what_col = ActiveCell.Column
' Set column width and row height
    Columns(what_col).ColumnWidth = wide_char
    Rows(what_row).RowHeight = height_pts
End Sub

 

4. Getting Cell Size

Public Sub get_size_info()
'Procedure to return activecell width and height in points'
'D Kelly O'Day  ProcessTrends.Com
Set rng = ActiveCell
Add = ActiveCell.Address
bsp;      Message = "Cell: " & Add & vbCrLf
         Message = Message & "Width = " & rng.Width & " Points" & vbCrLf
        Message = Message & "Height = " & rng.Height & " Points" & vbCrLf
out = MsgBox(Message, , "Cell Information")
End Sub