VerticalAlignment = xlTop also removes indent

[AlterEgo]

Hello all,

Running Office 2003 Professional Excel build 11.5612.5703 on XP sp2.

If VerticalAlignment is set to xlTop on a range of cells, and there are
indented cells it also removes the indent. If the property is set via the
UI, the correct indents are maintained.

To duplicate:

1. Enter text into a couple of cells.
2. Add a couple of indents to the cells.
3. Create and run the following macro:

Sub Test()

ActiveSheet.Cells.Select
Selection.VerticalAlignment = xlTop

End Sub

All of the indents will be removed. Wassup? Any help would be greatly
appreciated.

TIA,

Bill

 

[NickHK]

Yes, it does mess up the IndentLevel. Note also that you should use one of
the XlVAlign enums, namely xlVAlignTop. However, as xlVAlignTop and xlTop
have the same value, it does not effect your code:
It would appear this is what the .AddIndent property is for.

With Range("B3:B5")
.VerticalAlignment = xlVAlignTop
.AddIndent = True
End With

NickHK

 

[AlterEgo]

NickHK,

Thanks for your reply, unfortunately I don't think this will help me unless
I'm missing something. What I am trying to accomplish is to set up a
hierarchical numbering system that is controlled by the number of indents.
Users will add lines and indent them much like an outline. Then I go through
and number them according to the hierarchy.

01 - no indents
01.01
01.02 - two lines with one indent
10.02.01 - this line has 3 indents

I have always hated the fact that Excel defaults to a bottom alignment. It
in not intuitive when you have a wordwrap cell and the adjacent cell has its
data way in the bottom of the cell - exactly the opposite of how reports
normally work. I set up my style sheet to default to top alignment and
wordwrap, but that doesn't work either.

It appears as though I am going to have to itterate through each cell, store
its indentl level, align the cell and then re-apply the indent level. Please
let me know if you have another solution.

Thanks again for your reply,

Bill






I am trying to set the alignment on a Selection instead of having to
itterate each row.

 

[Peter T]

I have always hated the fact that Excel defaults to a bottom alignment.

So change your 'Normal' style to suit.

FWIW I couldn't replicate the problem.

Regards,
Peter T

 

[NickHK]

The code I posted sets the indent to whatever it was before the change in
VerticalAlignment.
So I don't see the problem.

However, Peter's suggestion is better, as then there will be no change
required, as it will default to top aligned.

NickHK