Howto: Make a Column resize on double click(MsFlexGrid) 2

[AvatarZA]

I am using a flexgrid as a data grid for formatting puposes(Not my idea but i have to use it)

I loop through the recordset of the field that was clicked getting the longest string or whatever it might be.

So i have the length but it is in characters, i know the font name, size. But the flexgrid uses Pixels to size.

I have made a column resize using the capital letter "W", i had one W resized the grid manually see how big it is then divide the colwidth by the number of W's i did that till 30, so if i only put W's in the grid the resize works perfectly, if you use numbers or other characters this method obviously doesn't work.

I was wondering if there is a way to be able to resize the column with the correct size no matter what the characters are. (Maybe check how big every character is and add the pixels up that way)

Any ideas will be good. Also whether there is a better place to put the code like in the column seperator(Like Windows Explorer)(I haven't seen a method for that)

Here is my code(I have changed the indentation a bit)

Private Sub MSFGrid_DblClick()

Dim rs1 As Recordset

On Error GoTo errHand

Set rs1 = CostClass.GetSalaries 'Get the recordset from dll
If rs1.EOF Then
Exit Sub
End If

rs1.MoveFirst
fieldValue = 0 'Globally declared
fieldValueHigh = 0 'Globally declared
Do Until rs1.EOF
fieldValue = Len(rs1.Fields(selectedCol).Value) 'Globally declared(selectedCol)

If fieldValue > fieldValueHigh Then
fieldValueHigh = fieldValue
End If
rs1.MoveNext
Loop
gridCaseSize
MSFGrid.ColWidth(selectedCol) = fieldValueHigh
rs1.Close

Exit Sub

errHand:
MsgBox Err.Description
Exit Sub

End Sub

Public Sub gridCaseSize()

Select Case fieldValueHigh
'The number that i multiply by is only for a "W"
'Maybe do this 26 times, that would just work
'for the letters and would take a long time.
'Nevermind caps or non caps.
'Any ideas?
Case 1: fieldValueHigh = fieldValueHigh * 270
Case 2: fieldValueHigh = fieldValueHigh * 218
Case 3: fieldValueHigh = fieldValueHigh * 195
Case 4: fieldValueHigh = fieldValueHigh * 191.25
Case 5: fieldValueHigh = fieldValueHigh * 186
Case 6: fieldValueHigh = fieldValueHigh * 182.5
Case 7: fieldValueHigh = fieldValueHigh * 177.85
Case 8: fieldValueHigh = fieldValueHigh * 176.25
Case 9: fieldValueHigh = fieldValueHigh * 175
Case 10: fieldValueHigh = fieldValueHigh * 174
Case 11: fieldValueHigh = fieldValueHigh * 173.18
Case 12: fieldValueHigh = fieldValueHigh * 172.5
Case 13: fieldValueHigh = fieldValueHigh * 171.92
Case 14: fieldValueHigh = fieldValueHigh * 171.43
Case 15: fieldValueHigh = fieldValueHigh * 171
Case 16: fieldValueHigh = fieldValueHigh * 170.625
Case 17: fieldValueHigh = fieldValueHigh * 170.29
Case 18: fieldValueHigh = fieldValueHigh * 170
Case 19: fieldValueHigh = fieldValueHigh * 169.74
Case 20: fieldValueHigh = fieldValueHigh * 169.5
Case 21: fieldValueHigh = fieldValueHigh * 169.29
Case 22: fieldValueHigh = fieldValueHigh * 169.09
Case 23: fieldValueHigh = fieldValueHigh * 168.91
Case 24: fieldValueHigh = fieldValueHigh * 168.75
Case 25: fieldValueHigh = fieldValueHigh * 168.6
Case 26: fieldValueHigh = fieldValueHigh * 168.46
Case 27: fieldValueHigh = fieldValueHigh * 168.33
Case 28: fieldValueHigh = fieldValueHigh * 168.21
Case 29: fieldValueHigh = fieldValueHigh * 168.62
Case 30: fieldValueHigh = fieldValueHigh * 168
Case Else
fieldValueHigh = fieldValueHigh * 165
End Select

End Sub

 

[sunaj]

How about:


----------------------------------------------------------
Private Function FindTextWidthInPixels(Msg As String, Optional FSize As Integer = 8, Optional TheFont As String = "MS Sans Serif", Optional Bold As Boolean = False) As Single
Dim F As StdFont, S As Integer

S = Me.ScaleMode
Me.ScaleMode = vbPixels
Set F = Me.Font
Me.FontBold = Bold
Me.FontSize = FSize
Me.Font = TheFont
FindTextWidthInPixels = Me.TextWidth(Msg)
Me.Font = F
Me.ScaleMode = S

End Function
----------------------------------------------------------- Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[AvatarZA]

Thanks sunaj,

I will try that out and see if i can get it right.

 

[AvatarZA]

It almost is perfect, still doesn't get the exact amount of twips but that could be because of the function that i am using. Is there maybe a better way to get the twips

Private Sub MSFGrid_DblClick()
Dim rs1 As Recordset

On Error GoTo errHand

Set rs1 = CostClass.GetSalaries
If rs1.EOF Then
Exit Sub
End If

rs1.MoveFirst
fieldValue = 0
fieldValueHigh = 0
Do Until rs1.EOF
fieldValue = Len(rs1.Fields(selectedCol).Value)

If fieldValue > fieldValueHigh Then
fieldValueHigh = fieldValue
fieldValueHighText = rs1.Fields(selectedCol).Value 'fieldValueHighText = Global string
End If
rs1.MoveNext
Loop

fieldValueHigh = PixelsToTwipsX(FindTextWidthInPixels(fieldValueHighText, 8, "MS Sans Serif", False))
MSFGrid.ColWidth(selectedCol) = fieldValueHigh
rs1.Close

Exit Sub

errHand:
Exit Sub
MsgBox Err.Description
End Sub

Function PixelsToTwipsX(Pixels As Long) As Long
PixelsToTwipsX = Pixels * Screen.TwipsPerPixelX
End Function

The other code is above in previous posts.

 

[phinoppix]

Hi, looking at this code part...

fieldValueHigh = PixelsToTwipsX(FindTextWidthInPixels(fieldValueHighText, 8, "MS Sans Serif", False))
    MSFGrid.ColWidth(selectedCol) = fieldValueHigh

You forgot the "margins" before and after the text inside the column.

MSFGrid.ColWidth(selectedCol) = fieldValueHigh + [inside margin]

If you would set the width equal to width of text, obviously the column borders will appear closest to the first and last character. I've seen this case with Datagrid. As to how to get the inside margin I can't help you right now. But I'm using a constant to do the trick. Something like...

Const COLUMN_INSIDE_MARGIN = 30    'twips

MSFGrid.ColWidth(selectedCol) = COLUMN_INSIDE_MARGIN + _
                                fieldValueHigh + _
                                COLUMN_INSIDE_MARGIN
' -or-

MSFGrid.ColWidth(selectedCol) = (COLUMN_INSIDE_MARGIN*2) + _
                                fieldValueHigh

 

[AvatarZA]

Thanks a lot for all the help

The resizing is now perfect, i am using a flex grid so i made the constant 45 twips.

Thanks some more.

Works well now.