Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
I'm in MS Access VBA, creating a spreadsheet.
Is there any way for me to determine if I have populated a numeric column with too many digits tb rendered? i.e. the sheet will show something like "#########" in the column? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
I don't work with Access, so I don't know if this can be translated over to
your environment or not; however, from code within Excel, you can test the Text property of a cell to see if the number fits or not. For example, something like this... MsgBox "Column too narrow? " & (Left(Range("H1").Text, 1) = "#") -- Rick (MVP - Excel) "PeteCresswell" wrote in message ... I'm in MS Access VBA, creating a spreadsheet. Is there any way for me to determine if I have populated a numeric column with too many digits tb rendered? i.e. the sheet will show something like "#########" in the column? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
Per Rick Rothstein:
I don't work with Access, so I don't know if this can be translated over to your environment or not; however, from code within Excel, you can test the Text property of a cell to see if the number fits or not. For example, something like this... MsgBox "Column too narrow? " & (Left(Range("H1").Text, 1) = "#") That'll work - 97% certain. I'll give it a shot. Thanks! -- PeteCresswell |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
That'll work - 97% certain.
Make that 100% - but only for fields that render "#" when overflowed. (i.e. Numeric and Date/Time). Now, how about a plain old Text field? e.g. .ColumnWidth = 5, but .Value = "Octagon Enhanced Loan Fund - Wachovia". The text renders OK as far as the column goes, but is truncated. Any way to identify that situation? Here's the code I have so far, which seems to deal with Numered/Date/ Time successfully: --------------------------------------------------------- Sub ExpandColumns() Dim curCell As Range Dim lastRow As Long Dim lastCol As Long Dim lastCell As Long Dim i As Long Dim R As Long Dim C As Long Dim curWid As Double Const incWid As Double = 0.1 Const maxWid As Long = 50 Application.ScreenUpdating = False If WorksheetFunction.CountA(Cells) 0 Then lastCol = Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column lastRow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row For R = 1 To lastRow For C = 1 To lastCol curWid = Columns(C).Width Set curCell = Cells(R, C) If Left(curCell.Text, 1) = "#" Then Do Until Left(curCell.Text, 1) < "#" curWid = curWid + incWid Columns(C).ColumnWidth = curWid / 10 Loop End If Next C Next R Application.ScreenUpdating = True Set curCell = Nothing End If End Sub --------------------------------------------------------- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
Now, how about a plain old Text field?
I figure it's got tb something that involves converting whatever units Column.ColumnWidth returns to the actual width of the text expressed in pixels, points or whatever Excel uses instead of number of characters. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"ColumnOverflow" Function?
On Oct 25, 10:43*am, PeteCresswell wrote:
Now, how about a plain old Text field? I figure it's got tb something that involves converting whatever units Column.ColumnWidth returns to the actual width of the text expressed in pixels, points or whatever Excel uses instead of number of characters. I'm going to spawn another thread on this. "How To Determine Length Of Variable-Font Text?" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text "comparison" operator for "contains" used in an "IF" Function | Excel Worksheet Functions | |||
Excel 2003: Conditional Formatting using "MIN" & "MAX" function | Excel Discussion (Misc queries) | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
inserting a conditional "go to" command on a excel "if" function | Excel Worksheet Functions | |||
Please add a "sheet" function like "row" and "column" functions | Excel Programming |