"ColumnOverflow" Function?

  • Thread starter Thread starter PeteCresswell
  • Start date Start date
P

PeteCresswell

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?
 
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) = "#")
 
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) = "#")
Click to expand...

That'll work - 97% certain.

I'll give it a shot.

Thanks!
 
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
---------------------------------------------------------
 
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 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.
Click to expand...

I'm going to spawn another thread on this.

"How To Determine Length Of Variable-Font Text?"
 
Back
Top