Solved

ms excel vba : is there a way to programticly determine if wrapped text in a cell is entirely visible or not?

Posted on 2013-06-24
5
1,299 Views
Last Modified: 2013-06-26
I am trying to cram as much text into small cells as possible ms. is there a way to programticly determine if wrapped text in a cell is entirely visible or not? if there was I could just keep checking and lowering the font size by a point until it fits. shrink to fit only works on unwrapped lines
0
Comment
Question by:Dov_B
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 30

Expert Comment

by:gowflow
ID: 39273884

is there a way to programticly determine if wrapped text in a cell is entirely visible or not?

What do you mean by that it is not clear

gowflow
0
 
LVL 2

Accepted Solution

by:
Agneau earned 500 total points
ID: 39273924
Hello Dov_B,

Unfortunately what your are asking for is not possible. Excel does not implement any property to evaluate if the cell's content is visible or not.

"Wrap text" and "Shrink to fit" are opposite concepts, that is why Excel disable the "Shrink to fit" option when you mark a cell with the "Wrap text" option.

Of course you can create some VBA code to mimic the shrinking feature in a wrapped cell, however such code won't be bullet proof.

Anyway you have down here a sample that implements what you want... but again, it's not bullet proof.

Sub ShrinkWrappedCell()
    Dim oldRowHeight As Double
    Dim oldFontSize As Double
    Dim newFontSize As Double
    
    oldRowHeight = ActiveCell.RowHeight
    oldFontSize = ActiveCell.Font.Size
    newFontSize = oldFontSize
    
    ActiveCell.Rows.AutoFit
    
    While ActiveCell.RowHeight > oldRowHeight
        newFontSize = newFontSize - 0.5
        ActiveCell.Font.Size = newFontSize
        ActiveCell.RowHeight = oldRowHeight
        ActiveCell.Rows.AutoFit
    Wend
        
End Sub

Open in new window


Regards
0
 

Author Comment

by:Dov_B
ID: 39274300
wow That is very clever coding! Genius!
well I fooled aaround with it a bit
Sub ShrinkWrappedCell()
    'Application.ScreenUpdating = False
    Dim oldRowHeight As Double
    Dim oldFontSize As Double
    Dim newFontSize As Double
    Dim resizeCell As Range
    Cells(1, 1).EntireRow.Insert
    ActiveCell.Offset(1).Select
    Set resizeCell = Cells(1, 1)
    
    resizeCell.WrapText = True
    resizeCell.RowHeight = ActiveCell.RowHeight
    oldRowHeight = ActiveCell.RowHeight
    oldFontSize = ActiveCell.Font.Size
    resizeCell.Font.Size = ActiveCell.Font.Size
    resizeCell.Font.Name = ActiveCell.Font.Name
    resizeCell.HorizontalAlignment = ActiveCell.HorizontalAlignment
    resizeCell.VerticalAlignment = ActiveCell.VerticalAlignment
    resizeCell = ActiveCell.Text
    newFontSize = oldFontSize
    
    resizeCell.Rows.AutoFit
    
    While resizeCell.RowHeight > oldRowHeight
        newFontSize = newFontSize - 0.5
        resizeCell.Font.Size = newFontSize
        resizeCell.RowHeight = oldRowHeight
        resizeCell.Rows.AutoFit
    Wend
    Dim k As Range
    Set k = ActiveCell
        ActiveCell.Font.Size = newFontSize
        'ActiveCell.RowHeight = oldRowHeight
'        ActiveCell.Rows.AutoFit
        Cells(1, 1).EntireRow.Delete
        
        Application.ScreenUpdating = False
        k.Select

Open in new window

my question is why aint it bullet proof
sometimes the resizeCell.Rows.AutoFit just doesnt autofit !! It resizes the row leaving part of the text cut off?!! any help is much appreciated.
0
 
LVL 2

Expert Comment

by:Agneau
ID: 39274426
Hello Dov_B,

The problem is that in some particular cases the routine may shrink the font up to 1, when the text is really big.
Nothing to worry but sometimes annoying.

Regards
0
 

Author Comment

by:Dov_B
ID: 39274611
I did notice that once I think that I fixed that (not sure),
did you notice that it does notalways autosize the row correctly and can sometimes leave the text to large
by the way in a different programing language I use there is a function called calcTextRec which figures out the dimensions of a string is there anything like that in vba? I did find something for width but not height
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

710 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