ljhodgett
asked on
Cell height on flexgrid
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
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
ASKER
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.
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.
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.
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.
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_ctl Grid 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
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_ctl
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
'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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
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
' 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
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.
>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.
Private Sub widths()
Dim INTROW As Integer
Dim INTCOL As Integer
Dim aryWidths() As Double
ReDim aryWidths(MSHFlexGrid1.Col
For INTROW = 0 To MSHFlexGrid1.Rows - 1
For INTCOL = 0 To MSHFlexGrid1.Cols - 1
If Form1.TextWidth(MSHFlexGri
aryWidths(INTCOL) = Form1.TextWidth(MSHFlexGri
End If
Next
Next
For INTCOL = 0 To MSHFlexGrid1.Cols - 1
MSHFlexGrid1.ColWidth(INTC
Next
End Sub
let us know if it works