I'm trying to create a macro that will confine my text to its own cell (ie so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
where neither the cell nor the text are resized, just the text cut off from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.

Select your range with the text you want to truncate then:

selection.offset(0,1).value=" "

Tim

"Steve" wrote in message
...
I'm trying to create a macro that will confine my text to its own cell (ie
so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off
look
where neither the cell nor the text are resized, just the text cut off
from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular
size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell
right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.

Hi Steve -

Similar to Tim's version. Click on any cell anywhere in the column that
contains the text and run this:

Sub cutOff()
With Range(Cells(ActiveSheet.UsedRange.Row, ActiveCell.Column), _
Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eLastCell).Row, _
ActiveCell.Column))
.Offset(0, 1) = Chr(32)
End With
End Sub
---
Jay


"Steve" wrote:

I'm trying to create a macro that will confine my text to its own cell (ie so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
where neither the cell nor the text are resized, just the text cut off from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.

Brilliant - worked first time!

"Tim Williams" wrote:

Select your range with the text you want to truncate then:

selection.offset(0,1).value=" "

Tim

"Steve" wrote in message
...
I'm trying to create a macro that will confine my text to its own cell (ie
so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off
look
where neither the cell nor the text are resized, just the text cut off
from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular
size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell
right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.

Thanks Jay!
Cheers
Steve

"Jay" wrote:

Hi Steve -

Similar to Tim's version. Click on any cell anywhere in the column that
contains the text and run this:

Sub cutOff()
With Range(Cells(ActiveSheet.UsedRange.Row, ActiveCell.Column), _
Cells(ActiveSheet.UsedRange.SpecialCells(xlCellTyp eLastCell).Row, _
ActiveCell.Column))
.Offset(0, 1) = Chr(32)
End With
End Sub
---
Jay


"Steve" wrote:

I'm trying to create a macro that will confine my text to its own cell (ie so
it doesn't spill over to the next). I know I could use alignment options
'text wrap' or 'shrink to fit' however I prefer what I call the cut-off look
where neither the cell nor the text are resized, just the text cut off from
view at the point that it extends beyond its cell border.

Reason for my preference: usually all I need to see is the first 3 letters
of a cell entry and I like to keep my cell and font cells to a regular size
to reduce mental fatigue.

The macro I created only worked with the particular cell I used in the
recording. I based it around the following 3 steps; move to next cell right,
insert space (thus preventing spillover) retreat to home cell

I suppose I could create a template with the whole sheet preset to this
arrangment but also need to do it retroactively to existing sheets.