MS FlexGrid (VERY URGENT PLEASE HELP)

[sig47]

I have two questions about the MSFlexGrid...
Umm.. what I am doing is that I am getting data from a text file, seperating the data that I need and I am displaying it in a MSFlexGrid Control...

Q 1. How do I allow the user to automatically edit the columns...
Q 2. How do I automatically resize the columns... depending upon the length of the contents...

I might have a few more questions... I will post if I remember them...
I am a total newbie with MSFlexGrid

Thanx

[RhinoBull]

For some answers check out link in my signature.

[sig47]

Well its a great thread but... I had a problem with one of the codes...

Dim i As Long
Dim p As Long
Dim newCell As String
Dim xl As Excel.Application
Set xl = CreateObject("excel.application")
    xl.Workbooks.Open (App.Path & "\Book1")
    DoEvents
    xl.Visible = True
For i = 1 To MSFlexGrid1.Rows - 1
    For p = 1 To MSFlexGrid1.Cols - 1
        MSFlexGrid1.Col = p
        MSFlexGrid1.Row = i
        newCell = Chr(i + 64) & p
        xl.Worksheets("Sheet1").Range(newCell).Value = MSFlexGrid1.Text
    Next 
Next

Here... I get an error with the following line...

xl.Workbooks.Open (App.Path & "\Book1")

Any clue...

Well the reference from you (Rhinobull) solves my first question... any clue about the second one...

Also I have one other question

Q 3. How do I autosize the control to the total width of the columns... I looked up in object browser and the width of the columns is achievable in "twips" and I have no clue about what twips are...

[RhinoBull]

...

xl.Workbooks.Open (App.Path & "\[B]Book1[/B]")

...

Book1 is NOT a valid file name - it needs to be something like Book1.xls

[sig47]

Oh thanx a lot... and I realised that you are an expert on MSFlexGrid Rhinobull... so could you help me out with the other questions?

[RhinoBull]

To autosize columns have a look at this very old thread (remember: Seaching forum saves tons of time )

http://www.vbforums.com/showthread.php?t=135276

[sig47]

Well I looked at that thread and its sort of confusing... I really can't make out anything....

I tried to put it in the keypress events and it gives me errors... Also... I think the code is for VB5

Sorry but I really am a newbie with the MSFlexGrid control

[si_the_geek]

Copy the code that Serge posted (not what I posted, that was poor code!) into your form, or a module if you prefer. Do not place it within a sub/function, put it just before/after one.

Immediately after you have filled the grid (where ever that is), add this line of code:

ResizeGrid MSFlexGrid1, Form1

...just change MSFlexGrid1 and Form1 to the names of your grid & form.

[RhinoBull]

Hey SI,
I'm surprised that users names from before the crash are not associated with those after the crash...
What's up with that? Probably I need to post this in the feedback. You can split this post if you want.

[si_the_geek]

I can't remember the exact reason, but the DB is all messed up for those posts and/or users, and there is no way to get them back without far too much manual work (so my 1300+ posts are 'gone'.. at least that's better than people like Megatron who lost far more, even tho their logins still work ).

[RhinoBull]

yea, I remember amount of noise people made after that but I thought if name exist then it should be easy enough to get total counts but I guess not.
Oh well...

[sig47]

I did actually use his code... it works during the formload... but I also want it to resize as the user is typing in letters... And that is why I call the function again in the Keypress event... but for some reason... it is a few characters behind... like it starts resizing after I press 3 characters... and by that time the words are already out of the column... I hope you understand what I am trying to say

[si_the_geek]

I see.. the problem in that case would be that you are reading every single row for every single column (which all takes time, as does calling a sub), just to see if the current column should be resized.

What you should do instead is simply check the size of the text in the current cell, and if it is bigger than the column width, resize the column. eg:

Dim lngNeededSize as Long
  With MSFlexGrid1 'rename if apt
    lngNeededSize = Me.TextWidth(.Text) + 100
    If lngNeededSize < .ColWidth(.Col) Then .ColWidth(.Col) = lngNeededSize
  End With

[sig47]

Last question.... If I were to use the code in here

http://www.vbforums.com/showthread.p...ighlight=Click

to read from an excel file, is there a way for me to automatically find the last column:row ratio for the highest filled cell... ???

Also... Si... Serges code doesn't work completely... it only moves the last column a little bit

[si_the_geek]

is there a way for me to automatically find the last column:row ratio for the highest filled cell... ???

I'm not sure what you mean by that.. if you want to get the actual used area from the Excel sheet, replace the ".Range("A1:F7").Copy" with ".UsedRange.Copy"

Also... Si... Serges code doesn't work completely... it only moves the last column a little bit

I'm not sure what you mean there either.. do you mean that only the last column is being changed? (they should all change if apt, but have a minimum of their size before the sub was called)
..or do you mean that th size change is not correct?

[sig47]

Well... I mean both.. the size change is not correct AND it doesn't work completely... It just stretches the last column a little bit... otherwise... it really doesn't work that well... I don't know why...

[jcis]

Are you using Pixels as your Form Scalemode?

[si_the_geek]

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:

Public Sub FlexGrid_AutoSizeColumns(ByRef pGrid As MSFlexGrid, _
                                    ByRef pForm As Form, _
                                    Optional ByVal pIncludeHeaderRows As Boolean = True, _
                                    Optional ByVal pAllowShrink As Boolean = True, _
                                    Optional ByVal pMinCol As Long = 0, _
                                    Optional ByVal pMaxCol As Long = -1, _
                                    Optional ByVal pBorderSize As Long = 8)
'Set flexgrid column widths to the minimum for viewing all text
 
'Note that this will not be accurate if Cells have different fonts,
'or if .FontWidth (or .CellFontWidth) has been set
 
'Parameters:
'  pGrid              - the grid to work with
'  pForm              - the form the grid is on
'  pIncludeHeaderRows - whether to take the width of text in FixedRows into account
'  pAllowShrink       - allow column widths to get smaller than current?
'  pMinCol            - the first column to work with
'  pMaxCol            - the last column to work with (-1 means the right-most column)
'  pBorderSize        - the number of pixels used as a border around text (seems like 8 to me!)
 
Dim lngMinCol As Long, lngMaxCol As Long, lngCurrRow As Long
Dim lngMinRow As Long, lngMaxRow As Long, lngCurrCol As Long
Dim lngMaxWidth As Long, lngCurrWidth As Long
Dim fntFormFont As StdFont
 
                            'Store current form font (so can restore later)
  Set fntFormFont = New StdFont
  Call CopyFont(pForm.Font, fntFormFont)
                            'Set font of form to same as grid, to get accurate values
  Call CopyFont(pGrid.Font, pForm.Font)
 
  With pGrid                'Set rows/columns to check
    lngMinCol = pMinCol
    lngMaxCol = IIf(pMaxCol = -1, .Cols - 1, pMaxCol)
    lngMinRow = IIf(pIncludeHeaderRows, 0, .FixedRows)
    lngMaxRow = .Rows - 1
                            'For each column in specified range..
    For lngCurrCol = lngMinCol To lngMaxCol
                                                '..set min allowed size based on options
      lngMaxWidth = IIf(pAllowShrink, 0, pForm.ScaleX(.ColWidth(lngCurrCol), vbTwips, pForm.ScaleMode))
 
      For lngCurrRow = lngMinRow To lngMaxRow   '..find widest text (in scalemode of the form)
        lngCurrWidth = pForm.TextWidth(.TextMatrix(lngCurrRow, lngCurrCol))
        If lngMaxWidth < lngCurrWidth Then lngMaxWidth = lngCurrWidth
      Next lngCurrRow
                                                '..as the scalemode of the form may differ, convert to twips
      lngMaxWidth = pForm.ScaleX(lngMaxWidth, pForm.ScaleMode, vbTwips)
                                                '..resize the column as apt (with specified border size)
      .ColWidth(lngCurrCol) = lngMaxWidth + (pBorderSize * Screen.TwipsPerPixelX)
    Next lngCurrCol
  End With
                            'Restore form font
  Call CopyFont(fntFormFont, pForm.Font)
 
End Sub
 
 
Public Sub CopyFont(ByVal pFontFrom As StdFont, ByRef pFontTo As StdFont)
'Copy the properties of a font object to another
 
  With pFontFrom
    pFontTo.Bold = .Bold
    pFontTo.Charset = .Charset
    pFontTo.Italic = .Italic
    pFontTo.Name = .Name
    pFontTo.Size = .Size
    pFontTo.Strikethrough = .Strikethrough
    pFontTo.Underline = .Underline
    pFontTo.Weight = .Weight
  End With
 
End Sub

Example usage:

FlexGrid_AutoSizeColumns MSFlexGrid1, Me

[WelshPunk]

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:

Public Sub FlexGrid_AutoSizeColumns(ByRef pGrid As MSFlexGrid, _
                                    ByRef pForm As Form, _
                                    Optional ByVal pIncludeHeaderRows As Boolean = True, _
                                    Optional ByVal pAllowShrink As Boolean = True, _
                                    Optional ByVal pMinCol As Long = 0, _
                                    Optional ByVal pMaxCol As Long = -1, _
                                    Optional ByVal pBorderSize As Long = 8)
'Set flexgrid column widths to the minimum for viewing all text
 
'Note that this will not be accurate if Cells have different fonts,
'or if .FontWidth (or .CellFontWidth) has been set
 
'Parameters:
'  pGrid              - the grid to work with
'  pForm              - the form the grid is on
'  pIncludeHeaderRows - whether to take the width of text in FixedRows into account
'  pAllowShrink       - allow column widths to get smaller than current?
'  pMinCol            - the first column to work with
'  pMaxCol            - the last column to work with (-1 means the right-most column)
'  pBorderSize        - the number of pixels used as a border around text (seems like 8 to me!)
 
Dim lngMinCol As Long, lngMaxCol As Long, lngCurrRow As Long
Dim lngMinRow As Long, lngMaxRow As Long, lngCurrCol As Long
Dim lngMaxWidth As Long, lngCurrWidth As Long
Dim fntFormFont As StdFont
 
                            'Store current form font (so can restore later)
  Set fntFormFont = New StdFont
  Call CopyFont(pForm.Font, fntFormFont)
                            'Set font of form to same as grid, to get accurate values
  Call CopyFont(pGrid.Font, pForm.Font)
 
  With pGrid                'Set rows/columns to check
    lngMinCol = pMinCol
    lngMaxCol = IIf(pMaxCol = -1, .Cols - 1, pMaxCol)
    lngMinRow = IIf(pIncludeHeaderRows, 0, .FixedRows)
    lngMaxRow = .Rows - 1
                            'For each column in specified range..
    For lngCurrCol = lngMinCol To lngMaxCol
                                                '..set min allowed size based on options
      lngMaxWidth = IIf(pAllowShrink, 0, pForm.ScaleX(.ColWidth(lngCurrCol), vbTwips, pForm.ScaleMode))
 
      For lngCurrRow = lngMinRow To lngMaxRow   '..find widest text (in scalemode of the form)
        lngCurrWidth = pForm.TextWidth(.TextMatrix(lngCurrRow, lngCurrCol))
        If lngMaxWidth < lngCurrWidth Then lngMaxWidth = lngCurrWidth
      Next lngCurrRow
                                                '..as the scalemode of the form may differ, convert to twips
      lngMaxWidth = pForm.ScaleX(lngMaxWidth, pForm.ScaleMode, vbTwips)
                                                '..resize the column as apt (with specified border size)
      .ColWidth(lngCurrCol) = lngMaxWidth + (pBorderSize * Screen.TwipsPerPixelX)
    Next lngCurrCol
  End With
                            'Restore form font
  Call CopyFont(fntFormFont, pForm.Font)
 
End Sub
 
 
Public Sub CopyFont(ByVal pFontFrom As StdFont, ByRef pFontTo As StdFont)
'Copy the properties of a font object to another
 
  With pFontFrom
    pFontTo.Bold = .Bold
    pFontTo.Charset = .Charset
    pFontTo.Italic = .Italic
    pFontTo.Name = .Name
    pFontTo.Size = .Size
    pFontTo.Strikethrough = .Strikethrough
    pFontTo.Underline = .Underline
    pFontTo.Weight = .Weight
  End With
 
End Sub

Example usage:

FlexGrid_AutoSizeColumns MSFlexGrid1, Me

Hi there, think I am missing something here. I copied your function into a module. I have a flexigrid on a form bound with a datacontrol that will populate 25 rows in 10 columns. Without any function call to resize columns and with a function call to resize columns I get the same result. Any ideas please?

Regards

[si_the_geek]

The most likely issue is that you have put the function call in the wrong place.

It needs to be after the text has been added to the grid.

I very rarely use data controls, so I don't know where that would be. It would be best to create a new thread in the VB6 forum to ask where it should go.

[WelshPunk]

The most likely issue is that you have put the function call in the wrong place.

It needs to be after the text has been added to the grid.

I very rarely use data controls, so I don't know where that would be. It would be best to create a new thread in the VB6 forum to ask where it should go.

Sorry, I just made the connection (no pun) this procedure works fine when populating a grid manualy. I had a data control on the form which was kinda messing things up. I took it out and did it all manually and it works great now. Cheers

[Henkensiefken]

Hi,

Great small Function ("FlexGrid_AutoSizeColumns") ...

... can I use it in a commercial Project?

That would be great =)

Regards,
Henk

[si_the_geek]

Welcome to VBForums

Using it in projects is fine, even if they are commercial projects.

There are only issues if the code is posted somewhere else without citing the source.

[Henkensiefken]

Welcome to VBForums

Using it in projects is fine, even if they are commercial projects.

There are only issues if the code is posted somewhere else without citing the source.

Great ... thank you so much ... I'll "only" use it in a Commercial Software Project =)

Have a nice day =)
Henk

[codebolt]

Thank you very much, this works great!

The only thing I was struggling with was the StdFont object, it was telling me that the type was undefined. I had to add a reference to "Standard OLE Types" before I could run it.