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,113 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
  • 2
  • 2
5 Comments
 
LVL 29

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
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 will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

757 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now