Annabella
asked on
Length of Text in a MSFLEXGRID Cell
I have an access database field which is a memo field. I use an ADO control (with a Select statement) and a MsFlexGrid to display the data from the field. Although I can store >255 characters in the database field, I cannot display > 255 characters in the grid cell. Is there a workaround for this or a useful tip. I have tried several grid types.
You can most certainly place as many characters as you like in a FlexGrid cell.. <smile>. The secret is to set your CellHeight/CellWidth property large enough to accomodate all the characters. The Wordwrap property = True is useful in doing this as well as the folowing (Auto) CellHeight API function from MSDN:
-------------------------- ---------- ---------- ---------- ---
HOWTO: Adjust RowHeight of MSFlexGrid to Accommodate WordWrap
Last reviewed: December 30, 1998
Article ID: Q178127
The information in this article applies to:
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
SUMMARY
The MSFlexGrid included with Visual Basic 5.0 has a WordWrap property that allows the user to determine whether text in a cell of the MSFlexGrid should be continued on a new line in the same cell if one or more words extends beyond the edge of the cell. However, the MSFlexGrid does not automatically adjust the RowHeight to accommodate the extra lines. This article illustrates how you can adjust the RowHeight of cells in the MSFlexGrid to accommodate text that wraps.
MORE INFORMATION
This example uses the SendMessage API function with the EM_GETLINECOUNT constant as an argument. When you use the EM_GETLINECOUNT message, SendMessage returns the number of lines an edit control (or TextBox) is currently displaying. This does not work directly on the MSFlexGrid because the MSFlexGrid is not intrinsic to Windows, as is the edit box. However, by using an edit box with the MSFlexGrid, you can use SendMessage to extend the functionality of the MSFlexGrid.
Step-by-Step Example
Create a new Standard EXE project.Form1 is created by default.
Click Components on the Project menu and check "Microsoft FlexGrid Control 5.0."
Add a Data control, a TextBox, an MSFlexGrid control and a CommandButton to the default form.
Set the following properties for the controls on the default form:
Name Property Value
---- -------- -----
Data1 DatabaseName NWIND.MDB
Data1 RecordSource Employees
MSFlexGrid1 AllowUserResizing 3 - flexResizeBoth
MSFlexGrid1 DataSource Data1
MSFlexGrid1 WordWrap True
Text1 MultiLine True
Text1 Visible False
NOTE: The database NWIND.MDB is installed into your Visual Basic
directory.
Add the following code to the code window of the default form:
Option Explicit
Private Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Const EM_GETLINECOUNT = &HBA
Private Sub Command1_Click()
Dim ColLoop As Long
Dim RowLoop As Long
'Turn off redrawing to avoid flickering
MSFlexGrid1.Redraw = False
For ColLoop = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.ColWidth(ColLo op) = 2500
For RowLoop = 0 To MSFlexGrid1.Rows - 1
ReSizeCellHeight RowLoop, ColLoop
Next RowLoop
Next ColLoop
'Turn redrawing back on
MSFlexGrid1.Redraw = True
End Sub
Public Sub ReSizeCellHeight(MyRow As Long, MyCol As Long)
Dim LinesOfText As Long
Dim HeightOfLine As Long
'Set MSFlexGrid to appropriate Cell
MSFlexGrid1.Row = MyRow
MSFlexGrid1.Col = MyCol
'Set textbox width to match current width of selected cell
Text1.Width = MSFlexGrid1.ColWidth(MyCol )
'Set font info of textbox to match FlexGrid control
Text1.Font.Name = MSFlexGrid1.Font.Name
Text1.Font.Size = MSFlexGrid1.Font.Size
Text1.Font.Bold = MSFlexGrid1.Font.Bold
Text1.Font.Italic = MSFlexGrid1.Font.Italic
Text1.Font.Strikethrough = MSFlexGrid1.Font.Strikethr ough
Text1.Font.Underline = MSFlexGrid1.Font.Underline
'Set font info of form to match FlexGrid control
Me.Font.Name = MSFlexGrid1.Font.Name
Me.Font.Size = MSFlexGrid1.Font.Size
Me.Font.Bold = MSFlexGrid1.Font.Bold
Me.Font.Italic = MSFlexGrid1.Font.Italic
Me.Font.Strikethrough = MSFlexGrid1.Font.Strikethr ough
Me.Font.Underline = MSFlexGrid1.Font.Underline
'Put the text from the selected cell into the textbox
Text1.Text = MSFlexGrid1.Text
'Get the height of the text in the textbox
HeightOfLine = Me.TextHeight(Text1.Text)
'Call API to determine how many lines of text are in text box
LinesOfText = SendMessage(Text1.hWnd, EM_GETLINECOUNT, 0&, 0&)
'Check to see if row is not tall enough
If MSFlexGrid1.RowHeight(MyRo w) < (LinesOfText*HeightOfLine) Then
'Adjust the RowHeight based on the number of lines in textbox
MSFlexGrid1.RowHeight(MyRo w) = LinesOfText * HeightOfLine
End If
End Sub
Press the F5 key to run the project.
Click Command1.
RESULT: Before clicking the button, all cells of the MSFlexGrid are the default size. Clicking the button runs code that processes each cell of the MSFlexGrid and increases the cell's height if necessary to display all of it's text. In this example, this is most apparent in the "Notes" column of the MSFlexGrid control.
NOTE: In order to receive the best results, you may also need to adjust the ColWidth property.
--------------------------
HOWTO: Adjust RowHeight of MSFlexGrid to Accommodate WordWrap
Last reviewed: December 30, 1998
Article ID: Q178127
The information in this article applies to:
Microsoft Visual Basic Professional and Enterprise Editions for Windows, versions 5.0, 6.0
SUMMARY
The MSFlexGrid included with Visual Basic 5.0 has a WordWrap property that allows the user to determine whether text in a cell of the MSFlexGrid should be continued on a new line in the same cell if one or more words extends beyond the edge of the cell. However, the MSFlexGrid does not automatically adjust the RowHeight to accommodate the extra lines. This article illustrates how you can adjust the RowHeight of cells in the MSFlexGrid to accommodate text that wraps.
MORE INFORMATION
This example uses the SendMessage API function with the EM_GETLINECOUNT constant as an argument. When you use the EM_GETLINECOUNT message, SendMessage returns the number of lines an edit control (or TextBox) is currently displaying. This does not work directly on the MSFlexGrid because the MSFlexGrid is not intrinsic to Windows, as is the edit box. However, by using an edit box with the MSFlexGrid, you can use SendMessage to extend the functionality of the MSFlexGrid.
Step-by-Step Example
Create a new Standard EXE project.Form1 is created by default.
Click Components on the Project menu and check "Microsoft FlexGrid Control 5.0."
Add a Data control, a TextBox, an MSFlexGrid control and a CommandButton to the default form.
Set the following properties for the controls on the default form:
Name Property Value
---- -------- -----
Data1 DatabaseName NWIND.MDB
Data1 RecordSource Employees
MSFlexGrid1 AllowUserResizing 3 - flexResizeBoth
MSFlexGrid1 DataSource Data1
MSFlexGrid1 WordWrap True
Text1 MultiLine True
Text1 Visible False
NOTE: The database NWIND.MDB is installed into your Visual Basic
directory.
Add the following code to the code window of the default form:
Option Explicit
Private Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Const EM_GETLINECOUNT = &HBA
Private Sub Command1_Click()
Dim ColLoop As Long
Dim RowLoop As Long
'Turn off redrawing to avoid flickering
MSFlexGrid1.Redraw = False
For ColLoop = 0 To MSFlexGrid1.Cols - 1
MSFlexGrid1.ColWidth(ColLo
For RowLoop = 0 To MSFlexGrid1.Rows - 1
ReSizeCellHeight RowLoop, ColLoop
Next RowLoop
Next ColLoop
'Turn redrawing back on
MSFlexGrid1.Redraw = True
End Sub
Public Sub ReSizeCellHeight(MyRow As Long, MyCol As Long)
Dim LinesOfText As Long
Dim HeightOfLine As Long
'Set MSFlexGrid to appropriate Cell
MSFlexGrid1.Row = MyRow
MSFlexGrid1.Col = MyCol
'Set textbox width to match current width of selected cell
Text1.Width = MSFlexGrid1.ColWidth(MyCol
'Set font info of textbox to match FlexGrid control
Text1.Font.Name = MSFlexGrid1.Font.Name
Text1.Font.Size = MSFlexGrid1.Font.Size
Text1.Font.Bold = MSFlexGrid1.Font.Bold
Text1.Font.Italic = MSFlexGrid1.Font.Italic
Text1.Font.Strikethrough = MSFlexGrid1.Font.Strikethr
Text1.Font.Underline = MSFlexGrid1.Font.Underline
'Set font info of form to match FlexGrid control
Me.Font.Name = MSFlexGrid1.Font.Name
Me.Font.Size = MSFlexGrid1.Font.Size
Me.Font.Bold = MSFlexGrid1.Font.Bold
Me.Font.Italic = MSFlexGrid1.Font.Italic
Me.Font.Strikethrough = MSFlexGrid1.Font.Strikethr
Me.Font.Underline = MSFlexGrid1.Font.Underline
'Put the text from the selected cell into the textbox
Text1.Text = MSFlexGrid1.Text
'Get the height of the text in the textbox
HeightOfLine = Me.TextHeight(Text1.Text)
'Call API to determine how many lines of text are in text box
LinesOfText = SendMessage(Text1.hWnd, EM_GETLINECOUNT, 0&, 0&)
'Check to see if row is not tall enough
If MSFlexGrid1.RowHeight(MyRo
'Adjust the RowHeight based on the number of lines in textbox
MSFlexGrid1.RowHeight(MyRo
End If
End Sub
Press the F5 key to run the project.
Click Command1.
RESULT: Before clicking the button, all cells of the MSFlexGrid are the default size. Clicking the button runs code that processes each cell of the MSFlexGrid and increases the cell's height if necessary to display all of it's text. In this example, this is most apparent in the "Notes" column of the MSFlexGrid control.
NOTE: In order to receive the best results, you may also need to adjust the ColWidth property.
wsh,
Nice one!
Have a look at this Q I've justed commented on
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=11729238
you may wish to jump in with your better answer............
:-)
If you don't I'll post a link there to this Q and claim the points without a pang of conscience!!!
btw, one of the things I had in mind when posting in this Q was that it may not necessarily be a good idea to always fetch memo fields back from a db. Sometimes it is preferable to get that data 'on demand'
Nice one!
Have a look at this Q I've justed commented on
https://www.experts-exchange.com/jsp/qShow.jsp?ta=visualbasic&qid=11729238
you may wish to jump in with your better answer............
:-)
If you don't I'll post a link there to this Q and claim the points without a pang of conscience!!!
btw, one of the things I had in mind when posting in this Q was that it may not necessarily be a good idea to always fetch memo fields back from a db. Sometimes it is preferable to get that data 'on demand'
If indeed you are working with Memo Blobs.. you may want to consider this code snippet from MSDN on how to load them into a Grid:
<----- Code Begin ----->
Public Function ShowData(Resultset As Recordset) As Variant
Dim cl As Field
Static GridSetup As Boolean
Dim MaxL As Integer
Dim Op As Integer
Dim rsl As Recordset
Dim rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup = False Then
FGrid1.rows = 51
FGrid1.Cols = rsl.Fields.Count
FGrid1.Row = 0
Op = 0
For Each cl In rsl.Fields
FGrid1.Col = Op
FGrid1 = cl.Name
If rsl.Fields(Op).DefinedSize > 255 Then
MaxL = 1
Else
MaxL = rsl.Fields(Op).ActualSize + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col ) = TextWidth(String(MaxL, "n"))
Op = Op + 1
Next cl
GridSetup = True
End If
FGrid1.rows = 1
FGrid1.rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
With FGrid1
' You can also use the ADO2 GetString method here in lieu of the
' following.
FGrid1.Clip = rsl.GetString(adClipString , 50, , , "-")
End With
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 3021:
FGrid1.Clear
Resume ExitShowData
Case 13, Is < 0
rows(j, i) = "< >"
Resume 'Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function
<----- Code End ----->
<----- Code Begin ----->
Public Function ShowData(Resultset As Recordset) As Variant
Dim cl As Field
Static GridSetup As Boolean
Dim MaxL As Integer
Dim Op As Integer
Dim rsl As Recordset
Dim rows As Variant
On Error GoTo ShowDataEH
Set rsl = Resultset
If GridSetup = False Then
FGrid1.rows = 51
FGrid1.Cols = rsl.Fields.Count
FGrid1.Row = 0
Op = 0
For Each cl In rsl.Fields
FGrid1.Col = Op
FGrid1 = cl.Name
If rsl.Fields(Op).DefinedSize
MaxL = 1
Else
MaxL = rsl.Fields(Op).ActualSize + 4
End If
If MaxL > 20 Then MaxL = 20
FGrid1.ColWidth(FGrid1.Col
Op = Op + 1
Next cl
GridSetup = True
End If
FGrid1.rows = 1
FGrid1.rows = 51
FGrid1.Row = 1
FGrid1.Col = 0
FGrid1.RowSel = FGrid1.rows - 1
FGrid1.ColSel = FGrid1.Cols - 1
With FGrid1
' You can also use the ADO2 GetString method here in lieu of the
' following.
FGrid1.Clip = rsl.GetString(adClipString
End With
ExitShowData:
FGrid1.RowSel = 1
FGrid1.ColSel = 0
Exit Function
ShowDataEH:
Select Case Err
Case 3021:
FGrid1.Clear
Resume ExitShowData
Case 13, Is < 0
rows(j, i) = "< >"
Resume 'Next
Case Else
MsgBox "Could not display data: " & Err & vbCrLf & Error$
Resume ' ExitShowData
End Select
End Function
<----- Code End ----->
ASKER
I tried all these and no luck at all. I am actually using the ADO Data control with a select statement as the recordsource. The grid is actually the MSHFLEXGRID. I tried the code you advised and it did work with the MSFLEXGRID and the label. Could this be a problem with the grid or am I missing something fundamental in a property. Also, is it possible to specify in the select statement that a field will return > 255 characters.
Greetings.
This question is still open today, perhaps it was overlooked or just lost in the volumes. Please return to this question to update it with comments if more information is needed to get your solution. If you've been helped by the participating expert(s), you may just convert their comment to the accepted answer and then grade and close. If an answer has ever been proposed you may not have this option to accept the comment as answer, if that is the case, ask the specific expert you wish to award to post an answer. This benefits others who then search our PAQ for just this solution, and rewards the experts who have provided information. A win/win scenario.
If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts. If you'd like to delete this question, use the same process as above, but explain why you think it should be deleted. Here is the Community Support link: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
You can always click on your profile to see all your open questions, in the event you also have other open items to be resolved. If your number of Questions Asked is not equal to the number of Answers Graded, choose to VIEW question history, and you'll quickly be able to navigate to your open items to close them as well.
I've had excellent help from experts-exchange through the years and find the real key to getting what I need is to remain active in all my questions, responding with results to suggestions until my solution is found, and recommend that highly.
Thank you very much for your responsiveness, it is very much appreciated.
":0) Asta
P.S. Some of the older questions from last year are not in the proper comment date order, and Engineering has been advised.
This question is still open today, perhaps it was overlooked or just lost in the volumes. Please return to this question to update it with comments if more information is needed to get your solution. If you've been helped by the participating expert(s), you may just convert their comment to the accepted answer and then grade and close. If an answer has ever been proposed you may not have this option to accept the comment as answer, if that is the case, ask the specific expert you wish to award to post an answer. This benefits others who then search our PAQ for just this solution, and rewards the experts who have provided information. A win/win scenario.
If you wish to award multiple participants, you can do so by creating a zero point question in the Community Support topic area, include this link and tell them which experts you'd like to award what amounts. If you'd like to delete this question, use the same process as above, but explain why you think it should be deleted. Here is the Community Support link: https://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
You can always click on your profile to see all your open questions, in the event you also have other open items to be resolved. If your number of Questions Asked is not equal to the number of Answers Graded, choose to VIEW question history, and you'll quickly be able to navigate to your open items to close them as well.
I've had excellent help from experts-exchange through the years and find the real key to getting what I need is to remain active in all my questions, responding with results to suggestions until my solution is found, and recommend that highly.
Thank you very much for your responsiveness, it is very much appreciated.
":0) Asta
P.S. Some of the older questions from last year are not in the proper comment date order, and Engineering has been advised.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can you not create a picture of a button face in the cell concerned with some text on it (i.e. 'Memo'). When the user clicks on that cell, fetch the data for the memo field from the database and display in a message box....
Just an idea.....perhaps it is even possible to emebed a button in the cell for this purpose......