Solved

Cell height on flexgrid

Posted on 2006-06-26
9
1,169 Views
Last Modified: 2009-07-29
Hi Experts.

I have a flexgrid on my form written in vb. Sometimes the data is too long to fit into the cell. I have switched wordwrap on but this just makes part of it disappear. I have had a look on the Microsoft website and there is a way of doing it but I couldn't get my head around it. Typical Microsoft jargon. Can someone explain / show me the code to do this please.

Many Thanks
Lee
0
Comment
Question by:ljhodgett
9 Comments
 
LVL 8

Expert Comment

by:hiteshgupta1
ID: 16983059
call this procedure in the Form_Load event or whereever u may like to

 Private Sub widths()
    Dim INTROW As Integer
    Dim INTCOL As Integer
   Dim aryWidths() As Double
   ReDim aryWidths(MSHFlexGrid1.Cols - 1)
   For INTROW = 0 To MSHFlexGrid1.Rows - 1
       For INTCOL = 0 To MSHFlexGrid1.Cols - 1
           If Form1.TextWidth(MSHFlexGrid1.TextMatrix(INTROW, INTCOL)) > aryWidths(INTCOL) Then
               aryWidths(INTCOL) = Form1.TextWidth(MSHFlexGrid1.TextMatrix(INTROW, INTCOL))
           End If
       Next
   Next
   For INTCOL = 0 To MSHFlexGrid1.Cols - 1
       MSHFlexGrid1.ColWidth(INTCOL) = aryWidths(INTCOL) + 250
   Next
End Sub

let us know if it works
0
 

Author Comment

by:ljhodgett
ID: 16983338
Hi hiteshgupta1

Thanks for getting back to me.

Its not quite what I'm looking for. The Widths are fixed as the flexgrid need to be printed onto A4. As a result if the text in the cell goes above a cetain amount I want it to wrap but be on 2 lines in the same cell.

Cheers
Lee.
0
 
LVL 20

Expert Comment

by:alainbryden
ID: 16984688
Allright. As I'm sure you know, you can't increase the height of individual cells, that would just look weird, but you can increase the height of on entire row. To do this use the following code:

MyFlexGrid.RowHeight(0) = MyFlexGrid.RowHeight(0) * 2

This will double the height of the first row. You can do it for any given row as long as you have the right index.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 9

Expert Comment

by:dancebert
ID: 16985099
Autosize RowHeight with WordWrap = True

This works with MsFlexGrid, is supposed to work the MSHFlexgrid, just change the As MSFlexGrid to As MSHFlexGrid
 
For this example you need a Form with 2 controls:
1. MSFlexGrid1
2. Label1

The Label1 control will be used the determine the correct height of the cell.
-----------------------------------------------------------------------

Option Explicit

Private Sub Form_Load()
  Dim lCol As Long, lRow As Long
 
  ' just add some data
  With MSFlexGrid1
    .Cols = 8
    .Rows = 8
    .WordWrap = True
    For lRow = 1 To .Rows - 1
      For lCol = 1 To .Cols - 1
        .TextMatrix(lRow, lCol) = "This line is to long and should wrap to the next line"
      Next lCol
    Next lRow
  End With
 
  FlexGridAutosizeRows MSFlexGrid1, Label1
End Sub


Public Sub FlexGridAutosizeRows(p_ctlGrid As MSFlexGrid, _
                        p_ctlLabel As Label, _
                        Optional ByVal p_lFirstRow As Long = -1, _
                        Optional ByVal p_lLastRow As Long = -1)

    '   For all cells:
    '   Stuff cell contents into a label that has autosize and wordwrap turned on, then
    ' read resulting height from label and apply to cell.

    Dim lCol As Long, lRow As Long
    Dim lCurCol As Long, lCurRow As Long
    Dim lRowHeight As Long

    ' Faster, if set at design time
'    With p_ctlLabel
'        .AutoSize = True
'        .WordWrap = True
'        .Visible = False
'    End With

    With p_ctlGrid
        .Redraw = False
        lCurCol = .Col
        lCurRow = .Row

        If p_lFirstRow = -1 Then p_lFirstRow = 0
        If p_lLastRow = -1 Then p_lLastRow = .Rows - 1

        For lRow = p_lFirstRow To p_lLastRow
            lRowHeight = 0
            .Row = lRow
            For lCol = 0 To .Cols - 1
                .Col = lCol
                lRowHeight = ResizeCellHeight(lRow, lCol, p_ctlGrid, p_ctlLabel, lRowHeight)
            Next lCol
           
            ' showtime
            ' Wg was chosen (I guess) because that's as wide and low as letters get
            .RowHeight(lRow) = lRowHeight + Me.TextHeight("Wg") / 5
        Next lRow

        .Row = lCurRow
        .Col = lCurCol
        .Redraw = True
    End With

End Sub



Private Function ResizeCellHeight(ByVal p_lRow As Long _
                , ByVal p_lCol As Long _
                , p_ctlGrid As MSFlexGrid _
                , p_ctlLabel As Label _
                , ByVal p_lRowHeight As Long) _
                As Long
               
    Dim lReturn As Long
   
    With p_ctlGrid
        p_ctlLabel.FontBold = .CellFontBold
        p_ctlLabel.FontName = .CellFontName
        p_ctlLabel.FontSize = .CellFontSize
        p_ctlLabel.FontBold = .CellFontBold
        ' Uncomment if using any of them
        'p_ctlLabel.FontItalic = .CellFontItalic
        'p_ctlLabel.FontStrikethru = .CellFontStrikeThrough
        'p_ctlLabel.FontUnderline = .CellFontUnderline
       
        p_ctlLabel.Width = .ColWidth(p_lCol)
        p_ctlLabel.Caption = .TextMatrix(p_lRow, p_lCol)
       
        If p_ctlLabel.Height > p_lRowHeight Then
            lReturn = p_ctlLabel.Height
        Else
            lReturn = p_lRowHeight
        End If
    End With
   
    ResizeCellHeight = lReturn
End Function


0
 
LVL 20

Expert Comment

by:alainbryden
ID: 16985217
Sorry dancebert, all that's incorrect.

WordWrap = True does not autosize the height of rows in a flexgrid, as the author of the question allready stated.

Quote: "I have switched wordwrap on but this just makes part of it disappear. "

The only way to change the height is to have the following code:

Dim I As Integer 'The index of the row
MyFlexGrid.RowHeight(i)= MyFlexGrid.RowHeight(i * 2
0
 
LVL 20

Accepted Solution

by:
alainbryden earned 250 total points
ID: 16985231
I also think it's awful, dancebert, that you copied your ENTIRE response from this webpage:

http://www.xtremevbtalk.com/showthread.php?threadid=35110

for shame.
0
 
LVL 9

Expert Comment

by:dancebert
ID: 16985804
alainbryden:

What's your problem?  I never claimed the answer was my code.  When I do provide an answer I prefer to do it as text rather than a link because links become dead over time.  

0
 
LVL 9

Expert Comment

by:dancebert
ID: 16985980
What this comment should say is:
' If you haven't set these at design time, this code must be uncommented.

    ' Faster, if set at design time
'    With p_ctlLabel
'        .AutoSize = True
'        .WordWrap = True
'        .Visible = False
'    End With

0
 
LVL 9

Expert Comment

by:dancebert
ID: 16985985
alainbryden:

>WordWrap = True does not autosize the height of rows in a flexgrid,
>as the author of the question allready stated.

You're right.  But the code works.  If you read it, you'd see that it stuffs the text into a label that has WordWrap and Autosize = true.  It then reads the height of the label and applies it to the FlexGrid's row height.  Oh, but don't take my word for it, just run the sample code.


>MyFlexGrid.RowHeight(0) = MyFlexGrid.RowHeight(0) * 2
>This will double the height of the first row. You can do it for
>any given row as long as you have the right index.

The original question said "Sometimes the data is too long to fit into the cell".  Your solution of hardcoding the row height will work if the length of the data is less than or equal to twice the cell width.  The downsides to your solution is that it will 1) double the row height whether needed or not, 2) fail to handle cell contents that require a 3rd (or more) row.  

0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
VB6 Compile Compatibility Issue 4 122
VBA/SQL - Connect to SQL server and pull data 4 137
Spell Check in VB6 13 137
Child Form in front 4 57
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question