Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
For Excel 2003, is there a way to determine the number of lines of text in a
cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Put all of the following code in a Module (Insert/Module from the VB
editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Rick:
Thanks. It's trying to work, but not quite there. I've set the two constants to appropriate numbers, but it's not accurately resetting the height. It is resizing the cell, but not enough. (The font and font size in for the cell are Tahoma, 26 pt.) I'm not getting any error messages; it just isn't expanding the height enough. Any suggestions? Does this need to be in it's own module, or can it be in a module with my other macros for this worksheet? Thanks again. Bert "Rick Rothstein" wrote in message ... Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
What numbers did you put in for the formulas? You did note the the numbers
are in pixels, not points, right? Did you want to specify the sizes in points instead? -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks. It's trying to work, but not quite there. I've set the two constants to appropriate numbers, but it's not accurately resetting the height. It is resizing the cell, but not enough. (The font and font size in for the cell are Tahoma, 26 pt.) I'm not getting any error messages; it just isn't expanding the height enough. Any suggestions? Does this need to be in it's own module, or can it be in a module with my other macros for this worksheet? Thanks again. Bert "Rick Rothstein" wrote in message ... Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Rick:
Thanks for your help with this. I've tried several numbers, but have settled in on minpixelshigh: 88, maxpixelshigh: 352. One question: the call to get the pointsperpixel: it seems to get the window closer to the right size if it's more pixelsperpoints. (The pointsperpixel value is 0.6. If I reverse the formula to pointsperpixel = lDotsPerInch / POINTS_PER_INCH (yielding 1.6), it _appears_ to be closer to the correct cell size, although the real problem, I think, seems to be with the autofit command. It does not expand the cell to the correct size, or at least does not seem to function as I would expect. Thanks again. Bert "Rick Rothstein" wrote in message ... What numbers did you put in for the formulas? You did note the the numbers are in pixels, not points, right? Did you want to specify the sizes in points instead? -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks. It's trying to work, but not quite there. I've set the two constants to appropriate numbers, but it's not accurately resetting the height. It is resizing the cell, but not enough. (The font and font size in for the cell are Tahoma, 26 pt.) I'm not getting any error messages; it just isn't expanding the height enough. Any suggestions? Does this need to be in it's own module, or can it be in a module with my other macros for this worksheet? Thanks again. Bert "Rick Rothstein" wrote in message ... Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Not sure what to tell you... the code I posted works perfectly on my system.
If it helps anyone else reading this thread to figure out what may be wrong, my PointsPerPixel value is 0.75... your value of 0.6 means you are using a Large Font setting (maybe 120 DPI) for your Windows display Font Size setting (I am using the Default Font setting of 96 DPI)... perhaps that has something to do with the problem. -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks for your help with this. I've tried several numbers, but have settled in on minpixelshigh: 88, maxpixelshigh: 352. One question: the call to get the pointsperpixel: it seems to get the window closer to the right size if it's more pixelsperpoints. (The pointsperpixel value is 0.6. If I reverse the formula to pointsperpixel = lDotsPerInch / POINTS_PER_INCH (yielding 1.6), it _appears_ to be closer to the correct cell size, although the real problem, I think, seems to be with the autofit command. It does not expand the cell to the correct size, or at least does not seem to function as I would expect. Thanks again. Bert "Rick Rothstein" wrote in message ... What numbers did you put in for the formulas? You did note the the numbers are in pixels, not points, right? Did you want to specify the sizes in points instead? -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks. It's trying to work, but not quite there. I've set the two constants to appropriate numbers, but it's not accurately resetting the height. It is resizing the cell, but not enough. (The font and font size in for the cell are Tahoma, 26 pt.) I'm not getting any error messages; it just isn't expanding the height enough. Any suggestions? Does this need to be in it's own module, or can it be in a module with my other macros for this worksheet? Thanks again. Bert "Rick Rothstein" wrote in message ... Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Rick:
I also realized that I had set the Zoom level to 75%. I think that could also affect the outcome because when I switched to 100%, it seemed to get more predictable. Also, you are correct about the larger display font size, so it seems that is another factor that could affect outcome. Thanks for all your help with this. Bert "Rick Rothstein" wrote in message ... Not sure what to tell you... the code I posted works perfectly on my system. If it helps anyone else reading this thread to figure out what may be wrong, my PointsPerPixel value is 0.75... your value of 0.6 means you are using a Large Font setting (maybe 120 DPI) for your Windows display Font Size setting (I am using the Default Font setting of 96 DPI)... perhaps that has something to do with the problem. -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks for your help with this. I've tried several numbers, but have settled in on minpixelshigh: 88, maxpixelshigh: 352. One question: the call to get the pointsperpixel: it seems to get the window closer to the right size if it's more pixelsperpoints. (The pointsperpixel value is 0.6. If I reverse the formula to pointsperpixel = lDotsPerInch / POINTS_PER_INCH (yielding 1.6), it _appears_ to be closer to the correct cell size, although the real problem, I think, seems to be with the autofit command. It does not expand the cell to the correct size, or at least does not seem to function as I would expect. Thanks again. Bert "Rick Rothstein" wrote in message ... What numbers did you put in for the formulas? You did note the the numbers are in pixels, not points, right? Did you want to specify the sizes in points instead? -- Rick (MVP - Excel) "Bert" wrote in message ... Rick: Thanks. It's trying to work, but not quite there. I've set the two constants to appropriate numbers, but it's not accurately resetting the height. It is resizing the cell, but not enough. (The font and font size in for the cell are Tahoma, 26 pt.) I'm not getting any error messages; it just isn't expanding the height enough. Any suggestions? Does this need to be in it's own module, or can it be in a module with my other macros for this worksheet? Thanks again. Bert "Rick Rothstein" wrote in message ... Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
count lines of text in a cell
Hi Rick,
will this work for merged cells? "Rick Rothstein" wrote: Put all of the following code in a Module (Insert/Module from the VB editor's menu bar). Change the Const statements in the FitText macro to the minimum and maximum number of pixels high you want to limit a row to. After you have done that, go back to the worksheet, select a cell with text in it and run the macro... it will keep the height within the bounds you set while changing the row height of the selected cell to accommodate the text (column width remains fixed). Private Declare Function GetDC Lib "user32" _ (ByVal hwnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" _ (ByVal hDC As Long, _ ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" _ (ByVal hwnd As Long, _ ByVal hDC As Long) As Long Private Const LOGPIXELSX = 88 'Pixels/inch in X Private Const POINTS_PER_INCH As Long = 72 Private Function PointsPerPixel() As Double Dim hDC As Long Dim lDotsPerInch As Long hDC = GetDC(0) lDotsPerInch = GetDeviceCaps(hDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lDotsPerInch ReleaseDC 0, hDC End Function Public Sub FitText() Dim PPP As Double Const MinPixelsHigh As Long = 17 Const MaxPixelsHigh As Long = 85 PPP = PointsPerPixel With ActiveCell .Rows.AutoFit If .RowHeight < MinPixelsHigh * PPP Then .RowHeight = MinPixelsHigh * PPP ElseIf .RowHeight MaxPixelsHigh * PPP Then .RowHeight = MaxPixelsHigh * PPP End If End With End Sub -- Rick (MVP - Excel) "Bert" wrote in message ... For Excel 2003, is there a way to determine the number of lines of text in a cell (with wordwrap turned on? (Similar to LineCount for a textbox.) I'm adding blocks of text to a cell, and--within limits--want to be able to resize the cell so the whole block will be visible. The text may or may not contain CR/LF characters, so there could be blank lines I'd like to have displayed and included in the line count. I'd prefer not to use autofit, so I can maintain a minimum rowheight, as well as keeping from going over a maximum rowheight. Thanks. Bert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow spaces between lines of text within cell | Excel Discussion (Misc queries) | |||
Multiple lines of text in a cell | Excel Discussion (Misc queries) | |||
Count the number of Lines in a "wrap text"-cell | Excel Discussion (Misc queries) | |||
Text lines in a cell | Excel Discussion (Misc queries) | |||
how many lines can be in wrap text within a cell? | Excel Worksheet Functions |