Autofit columns in DataGrid (Like Excel) 1

[CubeE101]

*Note: This began as a question...
In writing this question, I figured out the solution...
Though it really ticked me off in the process...
So... I decided to go ahead and post the question with the answer, in case anyone else needs the info...
(might save a few headaches ;-))

Is there a way to autofit columns in a data grid...

I am really starting to hate this control...

I did a keyword search and could not find anything like what I wanted... (auto size & auto fit)

I know you can set the column width, with...
DataGrid1.Columns(x).Width = y

But how do you loop through the cells and find the longest string length for each column?

using a recordset (rs)...
rs(x) returns a field, and rs.fields(x) returns a field... but how do you return a record (one of the rows)???

looking through the help file, I saw this...

DefColWidth Property
...
If you set the DefColWidth property to 0, the control automatically sizes all columns based on either the width of the column heading or the Size property setting of the underlying field, whichever is larger.

so i tried...
DataGrid1.DefColWidth = 0

...after I setup the recordset...

But that doesn't work either!!! (argghhhh)

I've almost got what I want using a flexgrid, but I was looking for more of the functionality of the datagrid (such as selecting a row, not a cell, at a time)

If I can't find a solution to this... I guess I can go back to using the FlexGrid, but this has just really started to annoy me now...

I know I can use Form.TextWidth(str) + padding to set the width... I just can't figure out how to access the individual data... (isn't that the purpose of a data grid???)

----------------------------------------------------------
----------------------------------------------------------
Ok... Now for the solution:
----------------------------------------------------------
----------------------------------------------------------

I knew you could loop through the Columns in a datagrid like...
For Each Column In DataGrid1.Columns

after a little (well.. alot more) digging...
I found that Column.CellText(bookmark) returns the cells text... but it requires a bookmark... (wtf is a bookmark???)

It turns out that a bookmark is some annoying layer they added just to irritate me... seriously, it has a few pros but I don't think there are enough of them to validate it... ANYWAY... Instead of doing this:
Text = Column.CellText(RowNum)
You have to do this:
Text = Column.CellText(DataGrid1.RowBookmark(RowNum))
(tell me that's not just flat out dumb... btw, the earlier attempt DOES return an ERROR)

So now you know how to get the cells' text, and how many columns there are...
But how do you get the number of Rows???
hmmm... I know!!! Instead of using a standard method like Rows, or Rows.Count, or even RowCount...
No... Let's call it DataGrid1.ApproxCount !!!

OK... so now you can loop through the cells...

Use the TextWidth property from the parent Form to get the pixel width...

BTW, I have the Scalemode set to 3 - Pixels

So... Here is what I came up with...

Sub AutoFitDG(DG As DataGrid)
  Dim C As Column, I As Integer, L As Single, Text As String
  For Each C In DataGrid1.Columns                      'Loop through Columns
    L = TextWidth(C.Caption)                           'Get the header's length
    For I = 0 To DG.ApproxCount - 1                    'Loop through Rows
      Text = C.CellText(DG.RowBookmark(I))             'Get the Cell's text
      If TextWidth(Text) > L Then L = TextWidth(Text)  'Check and get larger length
    Next
    C.Width = L + 10                                   'Set the Column Width, the 10 is for padding...
  Next
End Sub

OK... I'm done ranting for a while ;-)

Hope someone else out there finds this useful...
I know it gave me hell...

For the record, here is the FlexGrid method, which is MUCH simpler, or at least makes more sense to me...

Sub AutoFitFG(FG As MSFlexGrid)
  Dim X As Integer, Y As Integer, L As Single, Text As String
  For X = 1 To FG.Cols - 1                             'Loop through Columns (w/ 1 fixed col)
    L = TextWidth(FG.TextMatrix(0, X))                 'Get the header's length (w/ 1 fixed row)
    For Y = 1 To FG.Rows - 1                           'Loop through Rows
      Text = FG.TextMatrix(Y, X)                       'Get the Cell's text
      If TextWidth(Text) > L Then L = TextWidth(Text)  'Check and get larger length
    Next
    FG.colWidth(X) = ScaleX(L + 10, vbPixels, vbTwips) 'Set the Column Width, the 10 is for padding...
  Next
End Sub

*Note: My form's scalemode was set to 3 - Pixel, hence the ScaleX(L + 10, vbPixels, vbTwips) call...
If your form is set to Twips, you might have to take that out of the FlexGrid method, and apply it in reverse to the DataGrid method... such as: ScaleX(L + 10, vbTwips, vbPixels)

Then again, it might work fine...
If the columns appear really small, or really big, you'll know where to look ;-)

Have Fun, ;-)
-Josh

PS, if you have a better or prefered method, PLEASE post it...

I believe strongm has a method to return the TextWidth if your grid font is different from the form font, since TextWidth returns the Width of the text based on the form's font...
Check out this thread: thread222-806549


PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.