Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Go Back  Xtreme Visual Basic Talk > > > Excel: Check if the cell is too small for the text it contains


Reply
 
Thread Tools Display Modes
  #1  
Old 07-15-2011, 11:48 AM
Ultimatum Ultimatum is offline
Newcomer
 
Join Date: Jul 2011
Posts: 3
Default Excel: Check if the cell is too small for the text it contains


Greetings, all.

I am exporting text to a worksheet with fixed column widths and there instances where the text in the cell does not fit and will obviously overlap to the cell on the right.

I'd like to prevent that overlapping by reducing the font size of the cell if it is necessary. The problem is how to check if the text does not fit.

Any help is appreciated.
Reply With Quote
  #2  
Old 08-11-2011, 05:59 PM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

I'm not sure that it exist a bullet proof solution as an answer to that question. Instead of trying to control it when the text is imported You can consider to create a template file that has the correct columns' sizes.
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #3  
Old 10-10-2011, 07:44 PM
Ultimatum Ultimatum is offline
Newcomer
 
Join Date: Jul 2011
Posts: 3
Default

Thanks for the reply, DennisW, and sorry for this extremely late post. I am using a template, which is actually created by code, with fixed column widths. The problem is when text is sent to a cell, if it's too long (the end-user decides what to input) it will brake the border and overlap the next cell.

I guess my question is: Is there a way to tell if the text about to be sent to the cell won't fit in it?

If not: Is there a way to tell after the text has been sent to the cell whether it overlaps or not?

The idea is to re-size the font if necessary in order to make it fit.

Thanks again.
Reply With Quote
  #4  
Old 10-11-2011, 11:06 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Hi,

The usual standard approach is to resize the columns. If You need to keep the output on x pages when printing it You can manipulate it via the printing options.

Screen resolution have a major impact, resizing Excel's main window and the active sheets windows have also an impact on the size... I cannot imagine how a soluton would look like to meet this... Sorry...
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #5  
Old 01-12-2012, 09:27 PM
Ultimatum Ultimatum is offline
Newcomer
 
Join Date: Jul 2011
Posts: 3
Lightbulb Resolution

Greetings all and DennisW. Again a late post but I wanted to share the resolution that I stumbled upon by accident for the issue I had.

As I mentioned before, I needed to fit any amount of text within a cell, preventing it from overlapping over the next cell, re-sizing the font size if needed. It turns out that Excel has a cell option under "Text control" named "Shrink to fit" that, when enabled, automatically re-sizes the text entered in the cell to fit. I still have not found a way to programatically know whether the text has been shrunk, but the "Shrink to fit" option does exactly what I needed. The property can be set per cell or range, and it is called ShrinkToFit.

Thanks again, DennisW. Hope this information is useful to others.
Reply With Quote
  #6  
Old 01-17-2012, 11:32 AM
DennisW's Avatar
DennisW DennisW is offline
Junior Contributor
 
Join Date: Mar 2006
Location: Östersund, Sweden
Posts: 268
Default

Thanks for coming back and inform about the solution Do You think that the following VBA code will solve the remaining issue?

Code:
Dim rnSource As Excel.Range Dim rnCell As Excel.Range Set rnSource = ActiveSheet.Range("Test") For Each rnCell In rnSource If rnCell.ShrinkToFit = False Then Debug.Print rnCell.Address End If Next
__________________
Kind regards,
Dennis

.NET & Excel | 2nd edition PED | MVP
Reply With Quote
  #7  
Old 02-13-2012, 06:33 PM
vfx vfx is offline
Newcomer
 
Join Date: Jan 2012
Posts: 3
Default

Shrink to Fit is a good option. Another one, if you don't want varying font size throughout the worksheet, would be to store the column widths in variables or a Long array, then use Excel's AutoFit method to automatically adjust column widths to fit the text (without changing the font size). You could do this for each cell of data:
wksTarget.Cell(iMyRow iMyCol).EntireColumn.AutoFit
for a whole column at once:
wksTarget.Columns(iMyCol).AutoFit
or for a whole sheet at once
wksTarget.Columns.AutoFit

Depends on how specific you want to narrow down which data doesn't fit. Then re-check the widths to see if any of them increased.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
 
Excel: Check if the cell is too small for the text it contains
Excel: Check if the cell is too small for the text it contains
 
-->