Results 1 to 25 of 25

Thread: MS FlexGrid (VERY URGENT PLEASE HELP)

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    MS FlexGrid (VERY URGENT PLEASE HELP)

    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
    Last edited by sig47; Jan 6th, 2007 at 06:24 PM.

  2. #2

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid

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

    VB Code:
    1. Dim i As Long
    2. Dim p As Long
    3. Dim newCell As String
    4. Dim xl As Excel.Application
    5. Set xl = CreateObject("excel.application")
    6.     xl.Workbooks.Open (App.Path & "\Book1")
    7.     DoEvents
    8.     xl.Visible = True
    9. For i = 1 To MSFlexGrid1.Rows - 1
    10.     For p = 1 To MSFlexGrid1.Cols - 1
    11.         MSFlexGrid1.Col = p
    12.         MSFlexGrid1.Row = i
    13.         newCell = Chr(i + 64) & p
    14.         xl.Worksheets("Sheet1").Range(newCell).Value = MSFlexGrid1.Text
    15.     Next
    16. Next

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

    VB Code:
    1. 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...

  4. #4

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid

    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?

  6. #6

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid

    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

  8. #8
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid

    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:
    VB Code:
    1. ResizeGrid MSFlexGrid1, Form1
    ...just change MSFlexGrid1 and Form1 to the names of your grid & form.

  9. #9

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid

    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 ).

  11. #11

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid

    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

  13. #13
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid

    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:
    VB Code:
    1. Dim lngNeededSize as Long
    2.   With MSFlexGrid1 'rename if apt
    3.     lngNeededSize = Me.TextWidth(.Text) + 100
    4.     If lngNeededSize < .ColWidth(.Col) Then .ColWidth(.Col) = lngNeededSize
    5.   End With

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid

    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
    Last edited by sig47; Jan 6th, 2007 at 06:24 PM.

  15. #15
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid

    Quote Originally Posted by sig47
    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?

  16. #16

    Thread Starter
    Junior Member
    Join Date
    Dec 2006
    Posts
    18

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    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...

  17. #17
    PowerPoster jcis's Avatar
    Join Date
    Jan 2003
    Location
    Argentina
    Posts
    4,430

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    Are you using Pixels as your Form Scalemode?

  18. #18
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    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

  19. #19
    Member
    Join Date
    Nov 2010
    Posts
    40

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    Quote Originally Posted by si_the_geek View Post
    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
    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

  20. #20
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    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.

  21. #21
    Member
    Join Date
    Nov 2010
    Posts
    40

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    Quote Originally Posted by si_the_geek View Post
    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

  22. #22
    New Member
    Join Date
    Oct 2016
    Posts
    3

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    Hi,

    Great small Function ("FlexGrid_AutoSizeColumns") ...

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

    That would be great =)

    Regards,
    Henk

  23. #23
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,927

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    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.

  24. #24
    New Member
    Join Date
    Oct 2016
    Posts
    3

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    Quote Originally Posted by si_the_geek View Post
    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

  25. #25
    New Member
    Join Date
    Oct 2017
    Posts
    1

    Re: MS FlexGrid (VERY URGENT PLEASE HELP)

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width