Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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,468 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 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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
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.

705 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