Adjust DataGrids Column Width Based on Longest Field in Underlying Source

Category:

Database

Type:

Snippets

Difficulty:

Intermediate

Author:

Masino Sinaga

Version Compatibility: Visual Basic 6

More information:

When you display data from a database to a DataGrid, you will see that DataGrid's columns width cannot adjust to the longest data in the field. This following procedure can fix the problem.

Instructions: Copy the declarations and code below and paste directly into your VB project.

Declarations:
'nothing

Code:

Sub AturLebarKolomDataGrid(dg As DataGrid, _
    adoData As Adodc, _
    jlhRecord As Integer, _
    jlhField As Integer, _
    Optional AccForHeaders As Boolean)
'Prosedur untuk menyesuaikan ukuran/lebar kolom DataGrid
'Adjust your DataGrid's columns
'dg = DataGrid
'adData = Adodc control
'jlhRecord = Jumlah record (number of records)
'jlhField = Jumlah field  (number of fields)
'AccForHeaders = True or False
    Dim row As Long, col As Long
    Dim width As Single, maxWidth As Single
    Dim saveFont As StdFont, saveScaleMode As Integer
    Dim cellText As String
    'Langsung keluar dari prosedur jika tidak ada
    'record di DataGrid
    'If the number of records = 0 then exit from the sub
    If jlhRecord = 0 Then Exit Sub
    'Simpan huruf yang digunakan oleh form,
    'dan sesuaikan ke huruf di DataGrid.
    'Save the form's font for DataGrid's font
    'Kita membutuhkan ini dengan tujuan untuk
    'menggunakan metode dari TextWidth milik form.
    'We need this for form's TextWidth method
    Set saveFont = dg.Parent.Font
    Set dg.Parent.Font = dg.Font
    'Sesuaikan ScaleMode = vbTwips untuk form (parent).
    'Adjust ScaleMode to vbTwips for the form (parent).
    saveScaleMode = dg.Parent.ScaleMode
    dg.Parent.ScaleMode = vbTwips
    adoData.Recordset.MoveFirst
    maxWidth = 0
    For col = 1 To jlhField - 1
        adoData.Recordset.MoveFirst
        If AccForHeaders Then
            maxWidth = dg.Parent.TextWidth(dg.Columns(col).Text) + 200
        End If
        adoData.Recordset.MoveFirst
        For row = 1 To jlhRecord
            'Ambil teks dari sell DataGrid
            'Get the text from the DataGrid's cell
            If jlhField = 1 Then
            Else
                cellText = dg.Columns(col).Text
            End If
            'Hitung lebarnya, dan tetapkan untuk batas.
            'Fix the border...
            'Catatan: Tidak berlaku untuk "multiple-line text fields"
            'atau teks yang fieldnya mengandung dari banyak baris.
            'Not for "multiple-line text"...
            width = dg.Parent.TextWidth(cellText) + 200
            'Update lebar maksimum jika kita menemukan
            'sebuah string yang lebih lebar.
            'Update the maximum width if we found the wider string
            If width > maxWidth Then
               maxWidth = width
               dg.Columns(col).width = maxWidth
            End If
            adoData.Recordset.MoveNext
        Next row
        'Ubah lebar kolom sekarang...
        'Change the column width...
        dg.Columns(col).width = maxWidth 'kolom terakhir!
    Next col
    'Ganti property parent milik DataGrid
    'Change the DataGrid's parent property
    Set dg.Parent.Font = saveFont
    dg.Parent.ScaleMode = saveScaleMode
    adoData.Recordset.MoveFirst
End Sub  'End of sub