The likely cause of the size change not being correct is either a variation in ScaleMode as jcis mentioned (as the FlexGrid always uses Twips), or potentially a mix of fonts.. TextWidth uses the font settings of the Form, whereas you should actually check with the font of the FlexGrid.

As to only one of the columns being resized, I presume it is what I mentioned before - Serge's code will not reduce the column sizes, and the text in the unchanged columns is not big enough to warrant an increase in size.


I have updated my code from the other thread, so it's now decent! It takes into account the issues above, and contains several optional parameters so that you can easily customise the way it works:
VB Code:
  1. Public Sub FlexGrid_AutoSizeColumns(ByRef pGrid As MSFlexGrid, _

  2.                                     ByRef pForm As Form, _

  3.                                     Optional ByVal pIncludeHeaderRows As Boolean = True, _

  4.                                     Optional ByVal pAllowShrink As Boolean = True, _

  5.                                     Optional ByVal pMinCol As Long = 0, _

  6.                                     Optional ByVal pMaxCol As Long = -1, _

  7.                                     Optional ByVal pBorderSize As Long = 8)

  8. 'Set flexgrid column widths to the minimum for viewing all text

  9.  
  10. 'Note that this will not be accurate if Cells have different fonts,

  11. 'or if .FontWidth (or .CellFontWidth) has been set

  12.  

  13. 'Parameters:

  14. '  pGrid              - the grid to work with

  15. '  pForm              - the form the grid is on

  16. '  pIncludeHeaderRows - whether to take the width of text in FixedRows into account

  17. '  pAllowShrink       - allow column widths to get smaller than current?

  18. '  pMinCol            - the first column to work with

  19. '  pMaxCol            - the last column to work with (-1 means the right-most column)

  20. '  pBorderSize        - the number of pixels used as a border around text (seems like 8 to me!)

  21.  
  22. Dim lngMinCol As Long, lngMaxCol As Long, lngCurrRow As Long

  23. Dim lngMinRow As Long, lngMaxRow As Long, lngCurrCol As Long

  24. Dim lngMaxWidth As Long, lngCurrWidth As Long

  25. Dim fntFormFont As StdFont

  26.  
  27.                             'Store current form font (so can restore later)

  28.   Set fntFormFont = New StdFont

  29.   Call CopyFont(pForm.Font, fntFormFont)

  30.                             'Set font of form to same as grid, to get accurate values

  31.   Call CopyFont(pGrid.Font, pForm.Font)

  32.  
  33.   With pGrid                'Set rows/columns to check

  34.     lngMinCol = pMinCol

  35.     lngMaxCol = IIf(pMaxCol = -1, .Cols - 1, pMaxCol)

  36.     lngMinRow = IIf(pIncludeHeaderRows, 0, .FixedRows)

  37.     lngMaxRow = .Rows - 1

  38.                             'For each column in specified range..

  39.     For lngCurrCol = lngMinCol To lngMaxCol

  40.                                                 '..set min allowed size based on options

  41.       lngMaxWidth = IIf(pAllowShrink, 0, pForm.ScaleX(.ColWidth(lngCurrCol), vbTwips, pForm.ScaleMode))

  42.  
  43.       For lngCurrRow = lngMinRow To lngMaxRow   '..find widest text (in scalemode of the form)

  44.         lngCurrWidth = pForm.TextWidth(.TextMatrix(lngCurrRow, lngCurrCol))

  45.         If lngMaxWidth < lngCurrWidth Then lngMaxWidth = lngCurrWidth

  46.       Next lngCurrRow

  47.                                                 '..as the scalemode of the form may differ, convert to twips

  48.       lngMaxWidth = pForm.ScaleX(lngMaxWidth, pForm.ScaleMode, vbTwips)

  49.                                                 '..resize the column as apt (with specified border size)

  50.       .ColWidth(lngCurrCol) = lngMaxWidth + (pBorderSize * Screen.TwipsPerPixelX)

  51.     Next lngCurrCol

  52.   End With

  53.                             'Restore form font

  54.   Call CopyFont(fntFormFont, pForm.Font)

  55.  
  56. End Sub

  57.  
  58.  
  59. Public Sub CopyFont(ByVal pFontFrom As StdFont, ByRef pFontTo As StdFont)

  60. 'Copy the properties of a font object to another

  61.  
  62.   With pFontFrom

  63.     pFontTo.Bold = .Bold

  64.     pFontTo.Charset = .Charset

  65.     pFontTo.Italic = .Italic

  66.     pFontTo.Name = .Name

  67.     pFontTo.Size = .Size

  68.     pFontTo.Strikethrough = .Strikethrough

  69.     pFontTo.Underline = .Underline

  70.     pFontTo.Weight = .Weight

  71.   End With

  72.  
  73. End Sub
Example usage:
VB Code:
  1. FlexGrid_AutoSizeColumns MSFlexGrid1, Me