Find out, if test doesn't fit in a cell

Hi,

I'm automatically inserting text in Excel cells. The cells height and
width is fixed and shouldn't be changed.

I'd like to automatically reduce font size, if a text doesn't fit. 
I would use conditional formatting, but I guess that there's no Excel
function available that would help here. And for some reason the font
size can not be changed by conditional formatting on my system.

So I'd like to use VBA, but how?
The easiest way would be to determine, how much space a text needs and
compare it with the cell width. But how do I get the first parameter?

Another idea would be to set a cell to automatic word wrap. All I need
then is to get the current number of lines. If it's greater then one, I
would reduce the font size. But how to I get the number of lines from an
automaticalley wrapped text?

TIA,
Christian
0
Christian
3/6/2010 11:00:37 AM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1347 Views

Similar Articles

[PageSpeed] 46

Hi,

This is a bit of a scattergun approach. It loops though each cell of the 
used range testing the text width by autofitting the column width and 
reducing font size until the column is back to the original width

Sub Fit_Columns()
Dim c As Range
For Each c In ActiveSheet.UsedRange
If Len(c.Value) > 0 Then
oldwidth = c.ColumnWidth
Do
    c.EntireColumn.AutoFit
    If c.ColumnWidth > oldwidth Then
        c.Font.Size = c.Font.Size - 1
        c.EntireColumn.AutoFit
    Else
        c.ColumnWidth = oldwidth
    End If
Loop Until c.ColumnWidth <= oldwidth
  If c.ColumnWidth < oldwidth Then
        c.ColumnWidth = oldwidth
  End If
End If
Next
End Sub
-- 
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that 
introduces the fewest assumptions while still sufficiently answering the 
question.


"Christian Treffler" wrote:

> Hi,
> 
> I'm automatically inserting text in Excel cells. The cells height and
> width is fixed and shouldn't be changed.
> 
> I'd like to automatically reduce font size, if a text doesn't fit. 
> I would use conditional formatting, but I guess that there's no Excel
> function available that would help here. And for some reason the font
> size can not be changed by conditional formatting on my system.
> 
> So I'd like to use VBA, but how?
> The easiest way would be to determine, how much space a text needs and
> compare it with the cell width. But how do I get the first parameter?
> 
> Another idea would be to set a cell to automatic word wrap. All I need
> then is to get the current number of lines. If it's greater then one, I
> would reduce the font size. But how to I get the number of lines from an
> automaticalley wrapped text?
> 
> TIA,
> Christian
> .
> 
0
Utf
3/6/2010 12:33:01 PM
Begin by recording the row height
then insert the text in a cell and turn on autofit
then compare the new row height to the original 

If the height increased, you must reduce font size:

Sub FixHite()
Dim B1 As Range
Set B1 = Range("B1")
B1.Clear
MsgBox B1.EntireRow.Height
B1.Value = "Now is the time for all good men"
B1.WrapText = True
MsgBox B1.EntireRow.Height
End Sub

-- 
Gary''s Student - gsnu201001


"Christian Treffler" wrote:

> Hi,
> 
> I'm automatically inserting text in Excel cells. The cells height and
> width is fixed and shouldn't be changed.
> 
> I'd like to automatically reduce font size, if a text doesn't fit. 
> I would use conditional formatting, but I guess that there's no Excel
> function available that would help here. And for some reason the font
> size can not be changed by conditional formatting on my system.
> 
> So I'd like to use VBA, but how?
> The easiest way would be to determine, how much space a text needs and
> compare it with the cell width. But how do I get the first parameter?
> 
> Another idea would be to set a cell to automatic word wrap. All I need
> then is to get the current number of lines. If it's greater then one, I
> would reduce the font size. But how to I get the number of lines from an
> automaticalley wrapped text?
> 
> TIA,
> Christian
> .
> 
0
Utf
3/6/2010 12:34:01 PM
Hi,

I think you would like it to change the font directly after you enter
a value.
So I used the Worksheet-Change event, which should be added to the
codepage of the worksheet.



Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aWith As Double

    aWith = Target.ColumnWidth
    Application.ScreenUpdating = False
    Target.EntireColumn.AutoFit
    Do While Target.ColumnWidth > aWith
        Target.Font.Size = Target.Font.Size - 1
        Target.EntireColumn.AutoFit
    Loop
    If Target.EntireColumn.Width < aWith Then
        Target.ColumnWidth = aWith
    End If
    Application.ScreenUpdating = True
End Sub


HTH,

Wouter
0
Wouter
3/6/2010 1:38:40 PM
intresting idea
-- 
Gary''s Student - gsnu201001


"Wouter HM" wrote:

> Hi,
> 
> I think you would like it to change the font directly after you enter
> a value.
> So I used the Worksheet-Change event, which should be added to the
> codepage of the worksheet.
> 
> 
> 
> Private Sub Worksheet_Change(ByVal Target As Range)
> 
>     Dim aWith As Double
> 
>     aWith = Target.ColumnWidth
>     Application.ScreenUpdating = False
>     Target.EntireColumn.AutoFit
>     Do While Target.ColumnWidth > aWith
>         Target.Font.Size = Target.Font.Size - 1
>         Target.EntireColumn.AutoFit
>     Loop
>     If Target.EntireColumn.Width < aWith Then
>         Target.ColumnWidth = aWith
>     End If
>     Application.ScreenUpdating = True
> End Sub
> 
> 
> HTH,
> 
> Wouter
> .
> 
0
Utf
3/6/2010 4:46:05 PM
Gary''s Student schrieb:

> Begin by recording the row height
> then insert the text in a cell and turn on autofit
> then compare the new row height to the original

Ah, the "Try it and check what happens" method. I have rather hoped that
there is a method or property which gives me the information directly.
But you cannot have everything, obviously.

Thank you and Mike for that tip.

CU,
Christian
0
Christian
3/6/2010 5:47:21 PM
Wouter HM schrieb:

> So I used the Worksheet-Change event, which should be added to the
> codepage of the worksheet.

That was my plan, yes.

CU,
Christian
0
Christian
3/6/2010 5:47:58 PM
Reply:

Similar Artilces:

Cell Values Not There ???
I'm using VBA code in Excel 2007. I am having a strange problem. The cells have content. Some of them, but not all of them, are selected from Data Validation lists (drawn from a named range). However, when I try to access their values in VBA, it says they are empty!'' For example, the simple code: Debug.Print "Cell value is: " & ActiveSheet.Cells(2, 7).Value results in the output: Cell value is: I even tried the following code, to make sure it was not a problem with the ActiveSheet reference: ThisWorkbook.Sheets("Form").Cells(2, 7)....

Help getting SheetName into a cell
I need to refer to the sheet name in a formula and can't figure out how to do it. I can't find a Function which will do this. I did discover ActiveSheet.Name but I have been unsuccessful in getting it work in a function. Specifically I have sheets named "1.a" , "1.b" , "1.c" . . . "99.a" , "99.b" , "99.c" and need to get these names into cell C3 of each sheet. The sheets may not be in order and there may be missing sheets. Thanks in advance for any help. Omar Hi You could put somthing like this in a macro or attatch ...

conditional formatting of row based on cell
I want to apply conditional formatting to a whole row based on the contents of one cell in that row. For example, if cell L2 contains the word 'sport', i want the whole of row 2 to be shaded. I'm guessing this is pretty simple, but seem to be having a bit of a mental blockage. Any suggestions would be appreciated. Thanks :) formula is =$l$2="sport" "Katherine" <katherine@katherine.com> wrote in message news:107b01c372ef$4ec59e30$a101280a@phx.gbl... > > I want to apply conditional formatting to a whole row > based on the contents of one...

Calculations using empty cells
I have a spreadsheet with three columns: Current balance, withdrawals, and deposits. Current balance is determined by subtracting withdrawals and adding deposits to the balance in the line above. Suddenly I have started getting a cell entry #VALUE! in the current balance cell. A typical entry in the cell is for G43 would be: =G42-E43+F43 and the sudden new result is #VALUE. All three columns have cells formatted as numbers. HELP! On Thu, 6 Mar 2008 16:22:02 -0600, Hank in KC wrote: > I have a spreadsheet with three columns: Current balance, withdrawals, and > deposit...

Re: Is there a way to HIDE a row based on a value of a cell ?
Hi Reddance, Try instead: Sub Tester() Dim rng As Range Dim i As Long If LCase(Sheets("Ctrl").Range("A1").Value) _ <> "yes" Then Exit Sub End If With Sheets("ToPrint") For i = 5 To .UsedRange.Rows.Count Step 2 If Not rng Is Nothing Then Set rng = Union(rng, .Cells(i, "A")) Else Set rng = .Cells(i, "A") End If Next i End With If Not rng Is Nothing Then rng.EntireRow.Hidden = True End If ...

Find both matched and unmatched entries
Hi, I have two tables that I wish to find: 1) find matched data - copy that into a separate table 2) find unmatched data - copy that data into a separate table Can I achieve the above results with one query? I have three fields in each table that I would like to compare against: Client Data Status Any assistance with this would be greatly appreciated... Cheers, GLT Hi Yes you can use an append query to do this quite simple BUT you would end up with the same data in 2 tables which be a mistake. Maybe you could post some more details of what you are trying to do (the end result) a...

Last Cell
To All I was wondering if you could help me with my problem. If you have data in your worksheet and you say delete all the data in the last row, when you say F5 (Goto) "Special" and select "Last Cell" then it will always go to the data row that you have just deleted. There seems no way to get the "Last Cell" to update to where the last data is. Does anyone know to resolve this issue please. Thanks in advance. Deleting the data won't impact the "Last Cell". The "Last Cell" doesn't refer to the current data. It refers to the la...

Extend text to next cell
I am trying to get my text to run over to the next cell only in display not actual value of that cell. So if A1 says, "How now brown cow.", it should show over cells A2 and A3. However, I also need the carriage returns to be picked up. If I use the "Wrap Text" property it only shows the sentence in that cell. If I don't use it I get the [] (brackets) in place of the carriage return. Maybe you could merge those cells (A1:A3 or did you really mean A1:C1?) But be aware that merging cells causes trouble with sorting, pasting, and lots of other stuff. Format|cells|al...

Creating a list of info in A1 cell in multiple worksheets
How can I create a list of the info/contents in the A1 cell in multiple worksheets? I am trying to create a summary worksheet of the data from 90 worksheets and would like to be able to list the headers in the A1 cell vertically. 'Right click on the summary sheet tab, and paste in this macro. 'Edit where appropriate: Sub CreateSummary() 'Starting row for summary: i = 1 For Each Sheet In ThisWorkbook.Sheets If Sheet.Name <> Me.Name Then 'Control What column to place data in Me.Cells(i, "A").Value = Sheet.Range("A1").Value i = i + 1...

Data from non-adjacent cells as input for Linest
Hi, I would like to use data as input for Linest that is in the following cells: known y: A1 to A3, A10 to A12 known x: B1 to B3, B10 to B12 I have the feeling that this is almost answered in the following posts, but I do not understand it: http://groups.google.com/group/microsoft.public.excel/browse_thread/thread/fe4463f591797f44/4061a15313417929?lnk=gst&q=linest+#4061a15313417929 Regards, David ...

delay in displaying cell contents
Is there a simple function that can delay the display of the contents of a cell by a few seconds. I know that it can be done as VBA code but I dont know how to do it. Thanks in advance for any help. Dave Hi have a look at the Wait method in the VBA help -- Regards Frank Kabel Frankfurt, Germany "Dave" <dave-rawlins@beeb.net> schrieb im Newsbeitrag news:9995bde6.0410151425.1688ff24@posting.google.com... > Is there a simple function that can delay the display of the contents > of a cell by a few seconds. I know that it can be done as VBA code > but I dont know how...

how to find all matches in an array
I have a task list that in one column lists the person primarily responsible for it and in the 2nd column is the back up person. The 3rd column is the task. On another sheet I want to put in someones name and bring up all tasks they are responsible for and all the tasks they are backup for. What is an easy way to do this. Easiest way IMHO would be to use advanced filter http://www.contextures.com/xladvfilter01.html -- Regards, Peo Sjoblom (No private emails please) "Greg" <Greg@discussions.microsoft.com> wrote in message news:FA4CB596-6451-4569-81AF-24EC18C50180...

Find the number of years between two dates
I have aroster of players with thier date of births. I would like to automatically add their ages in another column Try this Formula, assuming that the dob is in cell A1 =DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days" regards Mukesh "duke" wrote: > I have aroster of players with thier date of births. I would like to > automatically add their ages in another column On Fri, 9 Sep 2005 19:47:02 -0700, "duke" <duke@disc...

grouping cells to sort by only one column
Hi, New to this so please bear with me. I have a spread sheet where I am listing names in column A and othe info relating to that name in columns B and C in the two rows below th name. I then start a different name in the next row down with the info in t two rows below that, and so forth. I want to be able to add more names in the future and be able to sor all of this by column A only. The problem I am having is the info i the other columns moves. I need it to somehow attatch and stay put wit it's corisponding name without moving. In other words how do I make everything from A1 to ...

Imported text file w/o unique identifiers, find duplicates
Imported a txt file w/o unique identifiers and have run a query to find possible duplicate records based on 3 seperate fields. How can I either update those fields that may be in error in the original table or append a new record w/o the duplicate data and delete the original record? In the following I need to determine which records to keep, which to delete and/or which to update. End Date Total Sheets Time Operator Name 3/3/2007 14 0:00 00094123 B D MCNARA 3/3/2007 14 0:00 00095681 NAME UNKNOWN 3/17/2007 84 0:01 00091234 C D S 3/17/2007 84 0:01 000...

How do I link many cells to one particular cell? #2
I am trying to link multiple cells to one particular cell and can't seem to do it. I want the multiple cells not just to have the same information but be connected to the same cell. I want multiple cells to take the information from ex. cell D4. So everytime i change cell D4 every cell that is linked to it changes. Do I have to input =D4 into every single cell that I want to link or is there an easy way to mass link? enter =$D$4 in one cell and copy it to the rest. The $ keeps the address from changing relatively. Are the cells on the same worksheet? If yes, you could select...

Unlocking Cells
Hi, I'd like to know if there's a simple way to unlock or lock cells. I'm not sure if "Lock" is the correct term here. For example, if you have a formula that has C$14. Is there an easy way to unlock (remove the "$") from the formula or to ad it without manually doing it? Thanks for your help! Lisa You can use a macro to change the reference style (absolute vs relative), but you can also do it manually. Select the portion of the formula (just a single cell reference or the entire formula if you want) and hit the F4 key to cycle through all 4 options. lj ...

How to fill cell with two combobox values?
I am looking for VB code for command button that fills two combo box values and a toggle value (Y/N) behind each other in one cell. Bart Excel 2003 ...

How do I restore the cell content window on my toobar?
tools options view ........formula bar -- Greetings from New Zealand Bill K "hutchc" <hutchc@discussions.microsoft.com> wrote in message news:4A53F690-97D4-43D6-A458-6AA82B95F575@microsoft.com... > From the View menu, choose Formula Bar hutchc wrote: -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html ...

AP to test USB
Hi: we wanna write an AP to test the all USB work on our target board. MIPSII CPU ,wince 500 BSP. but don't know how to start this test code? we have found some code at C:\WINCE500\PRIVATE\TEST\DRIVERS\USB\FUNCTION\USBTEST how can i use these codes to test my USB (host ,port,function,anyway prove my USB on board is OK)? or (another way to say this problem) how can i call extern "C" SHELLPROCAPI ShellProc(UINT uMsg, SPPARAM spParam) to test my USB? hope to get your response. any comment would be great help to us. Thanks BR vincent tsai ...

How to quote cells when "IF" word is involved
On Sheet1, I have a data set (C1:C100) based on the input of cell A1. When A1 has various method choices,say,"method 1","method 2","method 3", each cell in the data set will have a new value accordingly. (=IF(A1="method 1",formula1,IF(A1="method 2",formula2,formula3)) I want to quote those three data sets on Sheet2. A lousy way is I could just duplicate Sheet1, set A1 to different value and quote data sets from different sheets. Is it possible to quote three data sets simultaneously from Sheet1? Thanks, Ming Perhaps use a 2 variable data t...

Insert string into a Cell ?
Hi, How do I insert string into a cell ? Dim myString As string myString = "abcd" shp.CellsSRC(visSectionProp, visRowProp, visCustPropsValue) = myString 'This will yield with type mismatch error. Thanks. Hello m, You've got a reference to the cell but not the property of the cell object. So if you want to set the cell's formula you need to use that property. Also, as you're adding a string you need to wrap it up in quotes (note two double quotes in a row get evaluated as a single set of quotes). Have a go with the following: shp.CellsSRC(visSectionProp, ...

Dividing a cell in Excel 2000
I am embarrassed to ask, but here goes. About a year ago some of you on this forum explained how I can divide a cell without creating a whole new row. I can't remember how I did it. With a diagonal line? A horizontal line? A vertical line? Doesn't really matter; I just want to enter a number on each side of the divide. Much appreciation, Lois You can always find previous posts in the archives. Just enter your name or email address in the author field at http://groups.google.com/advanced_group_search?q=group:*excel* to see your posts and any replies. You might take ...

Two cells divided into next cell
Hope this makes sense. I have F column that totals down to F16. Then I have a total on D16. I need both totals to be divided and show up on G16. Every cell has $ Amts, but the only cells not totaled down the row are E,G and J. So G16 is blank. Is this possible to do and If so how. I don't even know the formula to Divide and I need this to automatically happen every time I Insert the Amts in the cells. Please help In G16: =D16/F16 "Day" <Day@discussions.microsoft.com> wrote in message news:62C49FCC-273A-425B-9EEF-DCF2109A0DD9@microsoft.com... > > ...

Macro to copy cell down
I want to create a macro that will copy the cell above to the active cell. OR the built-in key that will do that tenbob@optonline.net Bob A few seconds with the macro recorder set to "Relative Reference" gave me this. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 11/3/2003 by Gord Dibben ' ' Selection.Copy ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub Gord Dibben XL2002 On Tue, 04 Nov 2003 04:26:28 GMT, Bob K. <tenbob@optonline.net> wrote: >I want to create a mac...