Indent size

[Learner101b]

Is there a way to change the size of the Text Alignment Indent size? I would
like a smaller indent and Excel will not accept a '.5'

Thanks for the help,
Learner101b

 

[Jim Cone]

Use a leading space.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins - change text case with "XL Extras")



"Learner101b"
wrote in message
Is there a way to change the size of the Text Alignment Indent size? I would
like a smaller indent and Excel will not accept a '.5'

Thanks for the help,
Learner101b

 

[Gord Dibben]

No, you cannot change the indent below 1 or change the increments to decimals.

A <space> is about half the width of an indent.

You could run a macro on currently entered text or use event code to add a space
before/after the text in each cell as you entered it.

Post back if interested in either method........or both.


Gord Dibben MS Excel MVP

 

[Learner101b]

If it is not too much trouble, I would appreciate both methods. I am only
semi-technical, but I can follow directions. I appreciate your help.

 

[Gord Dibben]

Event code first...........................

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module. Alt + q to return to the Excel window. Indent is produced when
you enter text(numerics will not trigger the event) into a cell in any cell in
My_Range. Right or left indent is up to you. Just swap the two lines by
removing or adding an apostrophe.

Private Sub Worksheet_Change(ByVal Target As Range)
Const My_Range As String = "A1:A100" 'adjust to suit your needs
Dim cell As Range
On Error GoTo endit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(My_Range)) Is Nothing Then
With Target.SpecialCells(xlCellTypeConstants, _
xlTextValues)
Target.Value = Target.Value & " " 'right indent
'OR
'Target.Value = " " & Target.Value 'left indent
End With
End If

endit:
Application.EnableEvents = True
End Sub

Macro code next.....................................

Alt + F11 to open the VBEditor. CTRL = r to open the Project Explorer.
Right-click on your workbook/project and Insert>Module. Copy/paste the macro
below to that module.

Sub add_space()
Dim rngR As Range, rngRR As Range
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)
For Each rngR In rngRR
rngR.Value = rngR.Value & " "
'OR
'rngR.Value = " " & rngR.Value
Next
End Sub

Alt + q to return to the Excel window.

This macro can be run on any selected range by going to Tools>Macro>Macros and
"Run". You could assign it to a button or shortcut key.


Gord

 

[Learner101b]

Works like a charm. I can even follow the code sort-of.

I appreciate you taking your time to help me with this since it is not
crucial to the result, but I do like spreadsheets to look 'pretty.' I could
never have figured out the code on my own.

Thanks again,
Learner101b

 

[Gord Dibben]

Thanks for the feedback.

Gord

Works like a charm. I can even follow the code sort-of.

I appreciate you taking your time to help me with this since it is not
crucial to the result, but I do like spreadsheets to look 'pretty.' I could
never have figured out the code on my own.

Thanks again,
Learner101b