Set column width in pixels

Padawan

Active Member
Joined
Apr 9, 2002
Messages
395
Greetings Board,

XL 2003, Windows XL Pro

I'm working on a database project and need to match it to an existing XL sheet.

Is there any way to default XL to work with column widths (and row heights) in pixels.

In other words, if I set a width to 10, I would want it to set to 10 pixels. I know the screen will show me the pixel count if I drag and drop, but I want to be able to set the widths and heights.

Thank you.
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Was a bit more complicated than I anticipated. I hope somebody can come up with an easier solution...

Code:
Option Explicit

Private Declare Function GetDC Lib "user32" (ByVal hwnd As Long) As Long
Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hdc As Long, ByVal nIndex As Long) As Long
Private Declare Function ReleaseDC Lib "user32" (ByVal hwnd As Long, ByVal hdc As Long) As Long

Private Const LOGPIXELSX As Long = 88
Private Const LOGPIXELSY As Long = 90

Sub Example()
    Rows(2).RowHeight = PixelToPoints(100, "VERTICAL")
    SetColumnWidth PixelToPoints(100), Columns(2)
End Sub

Sub SetColumnWidth(Points As Single, Target As Range)
    Dim ItemWidth As Single, x As Single
    Application.ScreenUpdating = False
    ItemWidth = Target(1).Width
    If ItemWidth > Points Then
        Do Until Target(1).Width <= Points
            Target.ColumnWidth = Target.ColumnWidth - 0.14
        Loop
    Else
        Do Until Target(1).Width >= Points
            Target.ColumnWidth = Target.ColumnWidth + 0.14
        Loop
    End If
    Application.ScreenUpdating = True
End Sub

Function PixelToPoints(Pixels As Long, Optional Direction As String = "HORIZONTAL") As Single
    Dim DC As Long
    
    DC = GetDC(0)
    If UCase(Direction) = "HORIZONTAL" Then
        PixelToPoints = (72 / (GetDeviceCaps(DC, LOGPIXELSX))) * Pixels
    Else
        PixelToPoints = (72 / (GetDeviceCaps(DC, LOGPIXELSY))) * Pixels
    End If
    ReleaseDC 0, DC
End Function
 
Upvote 0
Tom

Let me digest this a little bit. I was simply looking for a default setting to adjust - not a UDF!

But a MAJOR THANK YOU for the time on this function. I'll work on it in an hour or two.

Best regards,
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top