Autosize DataGrid Columns 
Author Message
 Autosize DataGrid Columns

Once my Datagrid is filled up with data, I'd like to have the columns
resized so that they are just wide enough to contain the data (plus
perhaps a tad bit of white space) that is already in the cells.

I've Googled this till my ears are turning red and can't seem to find
a simple answer (at least not for free).

Thanks.

Webbiz



Fri, 02 Dec 2011 10:40:32 GMT  
 Autosize DataGrid Columns



Quote:
> Once my Datagrid is filled up with data, I'd like to have the columns
> resized so that they are just wide enough to contain the data (plus
> perhaps a tad bit of white space) that is already in the cells.

> I've Googled this till my ears are turning red and can't seem to find
> a simple answer (at least not for free).

> Thanks.

> Webbiz

OK, first off to size a column (i) of a datagrid the syntax is:
 MyDatagrid.Columns(i).width=NNN in twips (by default, otherwise in the
units of the containing form)

If you want to find out how wide a string is:
StringWidth=Textwidth("Hello World")

By default textwidth returns the width in the font, size and measurement
units of the parent form. You can also use it on a picturebox or the
printer.
If your datagrid does not have the same font and font size as the parent
form you can either try to scale it or better still create an invisible
picturebox with the same font & size as the datagrid and use
myPicBox.Textwidth(text)

You may find that you need to adjust the width slightly either by a fixed
margin or by a small percentage.



Fri, 02 Dec 2011 20:41:00 GMT  
 Autosize DataGrid Columns

Quote:
> Once my Datagrid is filled up with data, I'd like to have the columns
> resized so that they are just wide enough to contain the data (plus
> perhaps a tad bit of white space) that is already in the cells.

> I've Googled this till my ears are turning red and can't seem to find
> a simple answer (at least not for free).

Clive Lumb covered one solution. Another solution is to use tool tips to
show contents of wide columns when the user hovers over a cell. You can do
that by setting ToolTipText property when the mouse moves over certain
cells.

To determine the cell position based on mouse coordinates, use ColContaining
and RowContaining methods in MouseMove event.



Fri, 02 Dec 2011 23:14:50 GMT  
 Autosize DataGrid Columns
Thank you Clive.

This was very informative.  :-)

Webbiz

On Mon, 15 Jun 2009 14:41:00 +0200, "Clive Lumb"

Quote:



>> Once my Datagrid is filled up with data, I'd like to have the columns
>> resized so that they are just wide enough to contain the data (plus
>> perhaps a tad bit of white space) that is already in the cells.

>> I've Googled this till my ears are turning red and can't seem to find
>> a simple answer (at least not for free).

>> Thanks.

>> Webbiz

>OK, first off to size a column (i) of a datagrid the syntax is:
> MyDatagrid.Columns(i).width=NNN in twips (by default, otherwise in the
>units of the containing form)

>If you want to find out how wide a string is:
>StringWidth=Textwidth("Hello World")

>By default textwidth returns the width in the font, size and measurement
>units of the parent form. You can also use it on a picturebox or the
>printer.
>If your datagrid does not have the same font and font size as the parent
>form you can either try to scale it or better still create an invisible
>picturebox with the same font & size as the datagrid and use
>myPicBox.Textwidth(text)

>You may find that you need to adjust the width slightly either by a fixed
>margin or by a small percentage.



Sat, 03 Dec 2011 01:48:40 GMT  
 Autosize DataGrid Columns
This is interesting. Thank you.

:-)

Webbiz



Quote:


>> Once my Datagrid is filled up with data, I'd like to have the columns
>> resized so that they are just wide enough to contain the data (plus
>> perhaps a tad bit of white space) that is already in the cells.

>> I've Googled this till my ears are turning red and can't seem to find
>> a simple answer (at least not for free).

>Clive Lumb covered one solution. Another solution is to use tool tips to
>show contents of wide columns when the user hovers over a cell. You can do
>that by setting ToolTipText property when the mouse moves over certain
>cells.

>To determine the cell position based on mouse coordinates, use ColContaining
>and RowContaining methods in MouseMove event.



Sat, 03 Dec 2011 01:49:36 GMT  
 Autosize DataGrid Columns

Quote:
> Once my Datagrid is filled up with data, I'd like to have the columns
> resized so that they are just wide enough to contain the data (plus
> perhaps a tad bit of white space) that is already in the cells.

> I've Googled this till my ears are turning red and can't seem to find
> a simple answer (at least not for free).

> Thanks.

> Webbiz

This from a class module I use for auto formatting a MSHFlexgrid. It
can be adapted for a datagrid.
Put a label on the same from as the grid and set it;s autosize
property to true.
Cheers
Dave

Private IsLess                   As Boolean

Public Sub MSHFlexGrid_Format(Optional ExpandRowHeight As Boolean, _
                                Optional ExpandCurrentRowOnly As
Boolean)
On Error GoTo vbError

If Not IsRecordsetOpen("Format Grid Row Height") Then Exit Sub

Dim e As DataTypeEnum

Label.AutoSize = True
Label.Visible = False

If (Recordset.RecordCount = 0) Or _
   (Recordset.AbsolutePosition = -1) Then
    MSHFlexGrid.Clear
    Set MSHFlexGrid.Recordset = Nothing
    Exit Sub
Else
    vMyBookmark = Recordset.Bookmark
End If

Set MSHFlexGrid.Recordset = Recordset
With MSHFlexGrid
.Font.Name = "Arial"
If myColor1 = myColor2 Then
.BackColorFixed = &HC0FFFF
.ForeColorFixed = &H8000000D
.BackColorSel = &HC0FFFF
.ForeColorSel = vbHighlight
Else
.ForeColor = myColor1
.BackColor = myColor2
.BackColorFixed = myColor1
.ForeColorFixed = myColor2
.BackColorSel = myColor1
.ForeColorSel = myColor2
End If
.AllowUserResizing = flexResizeBoth
.MousePointer = flexHourglass
.RowHeight(0) = .RowHeight(1) * 1.5
End With

Dim iIndx As Long, z As Long
Dim i As Integer, x As Integer, y As Integer
Dim AP As Integer, AP500 As Integer

x = 0
y = 0
iIndx = 0

If Recordset.RecordCount < 2001 Then
    AP = 1
    AP500 = Recordset.RecordCount
ElseIf Recordset.AbsolutePosition < 2001 Then
    AP = 1
    If Recordset.RecordCount < 2001 Then
        AP500 = Recordset.RecordCount
    Else
        AP500 = 2000
    End If
Else
    AP = Recordset.AbsolutePosition
    If (AP + 1999) < Recordset.RecordCount Then
        AP500 = AP + 1999
    Else
        AP500 = Recordset.RecordCount
    End If
End If

With MSHFlexGrid
.Redraw = False
'z = .RowHeight(0)
z = 360
Do While x < .Cols
.Col = x
If x <> 0 Then e = Recordset.Fields(x - 1).Type
'For i = 0 To .Rows - 1
'set the headings *********
.Row = 0
Label.Caption = .Text
If Label.Width > 0 Then
    If Label.Width > iIndx Then
        iIndx = Label.Width
        If iIndx > 3500 Then
            y = (iIndx \ 3500)
            iIndx = 3500
            If (.RowHeight(i) < ((z * y) + (2 * z))) Then

                    .RowHeight(i) = ((z * y) + (1.5 * z))
                    .WordWrap = True
            Else
                .WordWrap = True
            End If
            y = 0
        End If
    End If
End If
'**************************
If ExpandCurrentRowOnly Then
    AP = Recordset.AbsolutePosition
    AP500 = Recordset.AbsolutePosition
End If

For i = AP To AP500
.Row = i
If x <> 0 Then
If (e = adDate) Or (e = adDBDate) Then
    If i > 0 Then
        Recordset.AbsolutePosition = .Row
        .Text = Format(Recordset.Fields(x - 1).Value, "dd mmmm, yyyy")
    End If
End If
If e = adSingle Then .Text = Format(.Text, "#0.00%")
If e = adCurrency Then .Text = Format(.Text, FormatCurrency(0, -1,
vbUseDefault, vbUseDefault, vbUseDefault))
If e = adDouble Then .Text = Format(.Text, "#0.00")

End If
Label.Caption = .Text
If Label.Width > 0 Then
If Label.Width > iIndx Then
iIndx = Label.Width
If iIndx > 3500 Then
y = (iIndx \ 3500)
iIndx = 3500
If (.RowHeight(i) < ((z * y) + (2 * z))) Then
    If ExpandRowHeight Then
        .RowHeight(i) = ((z * y) + (1.5 * z))
        IsLess = False
    End If
    .WordWrap = True
Else
.WordWrap = True
End If
y = 0
End If
End If
End If
Next i
.ColWidth(x) = (iIndx + 100)
x = x + 1
iIndx = 0
Loop
.ColWidth(0) = .RowHeight(0)
.Redraw = True
End With
MSHFlexGrid.MousePointer = flexDefault

Recordset.Bookmark = vMyBookmark
MSHFlexGrid.TopRow = Recordset.AbsolutePosition

Exit Sub

vbError:
If (Recordset.RecordCount > 0) Then Recordset.MoveFirst
MSHFlexGrid.MousePointer = flexDefault
eError
End Sub

Public Sub MSHFlexGrid_ReduceRowHeight()
On Error GoTo vbError
If Not IsRecordsetOpen("Reduce Grid Row Height") Then Exit Sub
If IsLess Then Exit Sub
With Recordset
    If .RecordCount > 0 Or .AbsolutePosition <> -1 Then
        MSHFlexGrid.Redraw = False
        Screen.MousePointer = vbHourglass
        Dim i As Integer
        Dim n As Double
        n = (MSHFlexGrid.RowHeight(0) / 3) * 2
        For i = 0 To .RecordCount - 1
            MSHFlexGrid.RowHeight(i + 1) = n
        Next i
        IsLess = True
        MSHFlexGrid.Redraw = True
        Screen.MousePointer = vbDefault
    End If
End With

Exit Sub
vbError:
Screen.MousePointer = vbDefault
eError
End Sub

Public Sub MSHFlexGrid_ExpandRowHeight()
On Error GoTo vbError
If Not IsRecordsetOpen("Expand Grid Row Height") Then Exit Sub
If EditMode Then Exit Sub
MSHFlexGrid_Format True
IsLess = False
Exit Sub
vbError:
eError
End Sub



Sat, 03 Dec 2011 16:59:15 GMT  
 
 [ 6 post ] 

 Relevant Pages 

1. Autosize DataGrid columns??

2. DataGrid - Autosizing columns to width of maximum value

3. Datagrid column autosize, is it gone?

4. ListView Autosizing Columns

5. How to autosize columns in MSFlexgrid

6. Autosize columns in flexgrid - Help!

7. How to autosize columns in MSFlexgrid!!

8. Autosize columns in flexgrid - Help!

9. How to *autosize* columns in FlexGrid

10. Listview Autosize: entirely hiding of columns possible?

11. Autosizing MSHFlexgrid columns

12. MSFlexGrid Autosize Columns

 

 
Powered by phpBB® Forum Software