A form sizing question (w/MSHFlexgrid)

[reaper1973]

I have some simple code that will bring up a small MSHFlexgrid in a form of its own. I want the form to have the width of the total of the columns in the grid. I have tried a few ways of doing this, but it will not work. Can someone help me, please?

Thanks,
Reaper

Option Explicit
Public DSN_Name As String
Public Table_Name As String
Public adoConn As ADODB.Connection
 
Private Sub Form_Load()
' Hide the MSHFlexgrid, connect to the database, run the select query, fill the grid with the values, and unhide the grid
    
    With MSHFlexGrid1
        .Visible = False
        adoData.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=" & DSN_Name & ";"
        adoData.CommandType = adCmdText
        adoData.RecordSource = "SELECT LEFT(RTRIM(docid),8) AS [Batch Number], COUNT(docid) AS [Batch Count(s)] from " & Table_Name & " GROUP BY LEFT(RTRIM(docid),8)"
        adoData.Refresh
        .ColWidth(0) = 0
        .Visible = True
    End With
    Call ChangeColumnWidth
 
End Sub
 
Public Sub ChangeColumnWidth()
' Sets column width to width of longest text, or column header, in column
    Dim Flex As MSHFlexGrid
    Dim lngMaxLength As Long
    Dim intCounter As Integer
    Dim lngLength As Long
    Dim lngWidth As Long
    Dim lngMinWidth As Long
    Dim i As Integer
    Dim Parent As Form
    
    Set Flex = MSHFlexGrid1
    Set Parent = Flex.Parent
 
    Flex.Redraw = False
    Flex.FocusRect = flexFocusNone
    
    ' This For...Loop will check to see if the text of the column header has the largest width _
    ' and set the width of the column to whichever is largest
    For i = 1 To Flex.Cols - 1
        Flex.Row = 0
        Flex.Col = i
        If (i <> Flex.Cols) Then
            intCounter = Flex.TopRow
            With Parent
                .Font = Flex.FontFixed
                .FontSize = Flex.CellFontSize
                .FontBold = Flex.CellFontBold
                .FontItalic = Flex.CellFontItalic
            End With
            
            ' Get minsize of column from column header
            lngMinWidth = Parent.TextWidth(Flex.TextMatrix(0, i))
            lngMaxLength = 0
            intCounter = 0
            
            ' Check the width of each row
            While intCounter < Flex.Rows - 1
                Flex.Row = intCounter
                With Parent
                    .Font = Flex.CellFontName
                    .FontSize = Flex.CellFontSize
                    .FontBold = Flex.CellFontBold
                    .FontItalic = Flex.CellFontItalic
                End With
                
                lngLength = Parent.TextWidth(Flex.TextMatrix(intCounter, i))
                
                ' Compare the column header width with the row width
                If lngMaxLength < lngLength Then
                    lngMaxLength = lngLength
                End If
                intCounter = intCounter + 1
            Wend
 
            lngWidth = lngMaxLength
            If lngWidth < lngMinWidth Then
                lngWidth = lngMinWidth
            End If
            
            ' Set the column width of each column, but, if the column is longer than 3500 twips _
            ' divide the column width by 4 to fit the width of the MSHFlexgrid
            If lngWidth >= 3500 Then
                Flex.ColWidth(i) = ((lngWidth / 4) + 100)
            Else
                Flex.ColWidth(i) = lngWidth + 100
            End If
        End If
    Next i
    
    Flex.FocusRect = flexFocusLight
    Flex.Redraw = True
 
End Sub

[John McKernan]

I posted an example here http://www.vbforums.com/showthread.p...hreadid=151709