Trailing Periods (Dot Leaders)
 

Is there any way to add the trailing periods, dot leaders to cells that
contain text and in the next cell a dollar amount, I have someone requesting
it so that it is easier for them to see which amount corresponds to that
description. If you manually insert them, you have to keep messing with how
many periods to type to line up to the next cell. Then when you make text
changes you have to play around with it again. Then, depending on your
printer, it prints differently and doesn't always line up.
--
Michelle

Trailing Periods (Dot Leaders)
 

FormatCellsNumber TabCustom @*.

Or a macro you can add to a button or shortcut.

Sub Leaders()
Selection.NumberFormat = "@*."
End Sub


Gord Dibben MS Excel MVP


On Tue, 28 Aug 2007 13:34:02 -0700, Michelle
wrote:

Is there any way to add the trailing periods, dot leaders to cells that
contain text and in the next cell a dollar amount, I have someone requesting
it so that it is easier for them to see which amount corresponds to that
description. If you manually insert them, you have to keep messing with how
many periods to type to line up to the next cell. Then when you make text
changes you have to play around with it again. Then, depending on your
printer, it prints differently and doesn't always line up.

Trailing Periods (Dot Leaders)
 

In addition the @* and the next character will fill-in the cell, this @*-
would fill in dashes and @* would be the characater.

"Gord Dibben" wrote:

FormatCellsNumber TabCustom @*.

Or a macro you can add to a button or shortcut.

Sub Leaders()
Selection.NumberFormat = "@*."
End Sub


Gord Dibben MS Excel MVP


On Tue, 28 Aug 2007 13:34:02 -0700, Michelle
wrote:

Is there any way to add the trailing periods, dot leaders to cells that
contain text and in the next cell a dollar amount, I have someone requesting
it so that it is easier for them to see which amount corresponds to that
description. If you manually insert them, you have to keep messing with how
many periods to type to line up to the next cell. Then when you make text
changes you have to play around with it again. Then, depending on your
printer, it prints differently and doesn't always line up.

Trailing Periods (Dot Leaders)
 

Michelle,

When a post goes unanswered it usually means that either what is asked is
not possible or it has not been described sufficiently. Your request is
clear enough so I assume that, although it is possible to do what you want
with numbers, it is not possible with text. Here is a workaround using an
event Macro:

Highlight Column F (or whatever column your currency will be in), and custom
format it as "*.£#,##0.00" without the quotes and using whatever currency
symbol you use.

Right-click on the sheet tab and select "View Code" copy and paste this
macro in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
'Replace E:E with your own description column if different

If Target.Rows.Count 1 Then Exit Sub
If Target.Columns.Count 1 Then Exit Sub

Application.EnableEvents = False

Target.Value = Target.Value & _
Application.Rept(".", 20 - Len(Target.Value) * 2)
'Increase 20 to a greater number if your column is
'wider than the standard 8.43
Cells(Target.Row, Target.Column + 1).Value = 0

Application.EnableEvents = True

End Sub

The excess periods in column E will be hidden because the cell in column F
has an entry. I assume that you have the gridlines turned off, if so there
will be a slight gap where the gridline would have been.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Michelle" wrote in message
...
Is there any way to add the trailing periods, dot leaders to cells that
contain text and in the next cell a dollar amount, I have someone
requesting
it so that it is easier for them to see which amount corresponds to that
description. If you manually insert them, you have to keep messing with
how
many periods to type to line up to the next cell. Then when you make text
changes you have to play around with it again. Then, depending on your
printer, it prints differently and doesn't always line up.
--
Michelle

Trailing Periods (Dot Leaders)
 

Sandy

I answered this post 8/28/2007

OP did want to add leaders to numbers, just the text in the column adjacent to
the numbers.


Gord


On Wed, 29 Aug 2007 15:17:04 +0100, "Sandy Mann"
wrote:

Michelle,

When a post goes unanswered it usually means that either what is asked is
not possible or it has not been described sufficiently. Your request is
clear enough so I assume that, although it is possible to do what you want
with numbers, it is not possible with text. Here is a workaround using an
event Macro:

Highlight Column F (or whatever column your currency will be in), and custom
format it as "*.£#,##0.00" without the quotes and using whatever currency
symbol you use.

Right-click on the sheet tab and select "View Code" copy and paste this
macro in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
'Replace E:E with your own description column if different

If Target.Rows.Count 1 Then Exit Sub
If Target.Columns.Count 1 Then Exit Sub

Application.EnableEvents = False

Target.Value = Target.Value & _
Application.Rept(".", 20 - Len(Target.Value) * 2)
'Increase 20 to a greater number if your column is
'wider than the standard 8.43
Cells(Target.Row, Target.Column + 1).Value = 0

Application.EnableEvents = True

End Sub

The excess periods in column E will be hidden because the cell in column F
has an entry. I assume that you have the gridlines turned off, if so there
will be a slight gap where the gridline would have been.

Trailing Periods (Dot Leaders)
 

So I see on Google...... damned Outlook Express!

I see also that Reno said that a custom format of @*. would fill the text
cell with dots. Funny I could have sworn that I tried that format before I
posted but obviously not.

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sandy

I answered this post 8/28/2007

OP did want to add leaders to numbers, just the text in the column
adjacent to
the numbers.


Gord


On Wed, 29 Aug 2007 15:17:04 +0100, "Sandy Mann"

wrote:

Michelle,

When a post goes unanswered it usually means that either what is asked is
not possible or it has not been described sufficiently. Your request is
clear enough so I assume that, although it is possible to do what you want
with numbers, it is not possible with text. Here is a workaround using an
event Macro:

Highlight Column F (or whatever column your currency will be in), and
custom
format it as "*.£#,##0.00" without the quotes and using whatever currency
symbol you use.

Right-click on the sheet tab and select "View Code" copy and paste this
macro in the sheet module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("E:E")) Is Nothing Then Exit Sub
'Replace E:E with your own description column if different

If Target.Rows.Count 1 Then Exit Sub
If Target.Columns.Count 1 Then Exit Sub

Application.EnableEvents = False

Target.Value = Target.Value & _
Application.Rept(".", 20 - Len(Target.Value) * 2)
'Increase 20 to a greater number if your column is
'wider than the standard 8.43
Cells(Target.Row, Target.Column + 1).Value = 0

Application.EnableEvents = True

End Sub

The excess periods in column E will be hidden because the cell in column F
has an entry. I assume that you have the gridlines turned off, if so
there
will be a slight gap where the gridline would have been.