Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
1,538 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 31

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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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