I have several sheets with a text box giving various instructions.
All of the text is left aligned. But the last line is "Copyright,
2012", and this last line I want right aligned. Of course, I can do
it through the Excel interface, but when I turn the macro recording
on, I get a blank macro. Does anyone know the VBA code?

Remember, I need to align only part of the text, not the entire text
box. I've tried ...

ActiveSheet.Shapes(1).select
Selection.characters(?,?).alignment

.... but it doesn't work.

TIA,
Dom

Dom submitted this idea :
I have several sheets with a text box giving various instructions.
All of the text is left aligned. But the last line is "Copyright,
2012", and this last line I want right aligned. Of course, I can do
it through the Excel interface, but when I turn the macro recording
on, I get a blank macro. Does anyone know the VBA code?

Remember, I need to align only part of the text, not the entire text
box. I've tried ...

ActiveSheet.Shapes(1).select
Selection.characters(?,?).alignment

... but it doesn't work.

TIA,
Dom

Textbox alignment applies to the entire contents. To implement
indentation you need to 'pad' the line with spaces. What you want to do
here, then, is provide enough padding so the text 'appears' to be
right-aligned.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

On Jun 16, 11:17*am, GS wrote:
Dom submitted this idea :





I have several sheets with a text box giving various instructions.
All of the text is left aligned. *But the last line is "Copyright,
2012", and this last line I want right aligned. *Of course, I can do
it through the Excel interface, but when I turn the macro recording
on, I get a blank macro. *Does anyone know the VBA code?

Remember, I need to align only part of the text, not the entire text
box. *I've tried ...

ActiveSheet.Shapes(1).select
Selection.characters(?,?).alignment

... but it doesn't work.

TIA,
Dom

Textbox alignment applies to the entire contents. To implement
indentation you need to 'pad' the line with spaces. What you want to do
here, then, is provide enough padding so the text 'appears' to be
right-aligned.

--
Garry

Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text -

- Show quoted text -

But why then is it possible to right align a single line of text using
the EXCEL interface? That is, I can select the line, hit the right
align button, and it is done. It should be possible to do this in VBA.

Dom

I cannot replicate this in a textbox whether 2003 or 2007 versions.

I select a line out of a multiline string and hit right-align
icon.............all text aligns right.

Is this a new feature of Excel 2010?


Gord Dibben MS Excel MVP


On Thu, 16 Jun 2011 08:19:43 -0700 (PDT), Dom wrote:

But why then is it possible to right align a single line of text using
the EXCEL interface? That is, I can select the line, hit the right
align button, and it is done. It should be possible to do this in VBA.

On Jun 16, 2:18*pm, Gord Dibben wrote:
Dom

I cannot replicate this in a textbox whether 2003 or 2007 versions.

I select a line out of a multiline string and hit right-align
icon.............all text aligns right.

Is this a new feature of Excel 2010?

Gord Dibben * * MS Excel MVP



On Thu, 16 Jun 2011 08:19:43 -0700 (PDT), Dom wrote:
But why then is it possible to right align a single line of text using
the EXCEL interface? *That is, I can select the line, hit the right
align button, and it is done. *It should be possible to do this in VBA..- Hide quoted text -

- Show quoted text -

I'm using 2007. Are we talking about the same thing? By textbox, I
mean the textbox shape, what you get from the INSERT ribbon. Not the
multiline text box you might see on a form. Also, you must have an
actual new line, not just text that has wrapped automatically. For
example, try "This is<CRa new line". You can make "This is" align
left, and "a new line" align right.

Dom explained :
On Jun 16, 2:18*pm, Gord Dibben wrote:
Dom

I cannot replicate this in a textbox whether 2003 or 2007 versions.

I select a line out of a multiline string and hit right-align
icon.............all text aligns right.

Is this a new feature of Excel 2010?

Gord Dibben * * MS Excel MVP



On Thu, 16 Jun 2011 08:19:43 -0700 (PDT), Dom wrote:
But why then is it possible to right align a single line of text using
the EXCEL interface? *That is, I can select the line, hit the right
align button, and it is done. *It should be possible to do this in VBA.-
Hide quoted text -

- Show quoted text -

I'm using 2007. Are we talking about the same thing? By textbox, I
mean the textbox shape, what you get from the INSERT ribbon. Not the
multiline text box you might see on a form. Also, you must have an
actual new line, not just text that has wrapped automatically. For
example, try "This is<CRa new line". You can make "This is" align
left, and "a new line" align right.

I see what you're talking about. Not sure but I believe you may want to
be looking at the OLEObject properties for the Shape object.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

You are correct Dom.

I tested with wrapped text only.

Aploogies for that.

As for how to get this effect using VBA, I have no idea.


Gord

On Thu, 16 Jun 2011 11:40:39 -0700 (PDT), Dom wrote:

I'm using 2007. Are we talking about the same thing? By textbox, I
mean the textbox shape, what you get from the INSERT ribbon. Not the
multiline text box you might see on a form. Also, you must have an
actual new line, not just text that has wrapped automatically. For
example, try "This is<CRa new line". You can make "This is" align
left, and "a new line" align right.

"Dom" wrote in message
...
[ ]

I'm using 2007. Are we talking about the same thing? By textbox, I
mean the textbox shape, what you get from the INSERT ribbon. Not the
multiline text box you might see on a form. Also, you must have an
actual new line, not just text that has wrapped automatically. For
example, try "This is<CRa new line". You can make "This is" align
left, and "a new line" align right.


-----------

Using the xl2010 macro recorder, I get this:

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHo rizontal, 153,
38.25, 150, _
144.75).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Chara cters.Text = _
"This is " & Chr(13) & "A new Line" & Chr(13) & ""
With Selection.ShapeRange(1).TextFrame2.TextRange.Chara cters(1, 9).
_
ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignRight
End With

HTH!

--
Clif McIrvin

(clare reads his mail with moe, nomail feeds the bit bucket :-)