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 :-)

On Jun 16, 6:16*pm, "Clif McIrvin" wrote:
"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 :-)

Thanks, Cliff, but I'm guessing this only works in 2010, not 2007. I
get an "Object doesn't support this property" error.

Thanks, again, but I think it's a lost cause.

Dom

"Dom" wrote in message
...
On Jun 16, 6:16 pm, "Clif McIrvin" wrote:
"Dom" wrote in message

...
[ ]


Thanks, Cliff, but I'm guessing this only works in 2010, not 2007. I
get an "Object doesn't support this property" error.

Thanks, again, but I think it's a lost cause.

Dom

------

Bummer. I never ran 2007 so have no information. If you get no other
bites, you might try re-posting with a subject indicating 2007 and the
object name and see if that gets better response.

--
Clif McIrvin

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

Dom explained on 6/17/2011 :
On Jun 16, 6:16*pm, "Clif McIrvin" wrote:
"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 :-)

Thanks, Cliff, but I'm guessing this only works in 2010, not 2007. I
get an "Object doesn't support this property" error.

Thanks, again, but I think it's a lost cause.

Dom

And so is why I suggested padding the 2nd line so it 'appears'
right-aligned. This has been a long standing workaround in VB for form
controls and so I don't see why it won't work in VBA. Nice to hear,
though, that 2010 has finally evolved to provide built-in capability
for this.<g

One thing I've found interesting is how often things that work in an
earlier version get axed in later versions. I'm speaking primarily
about v10 (xl2002). Though it's usually a case of the reverse scenario
(newer versions having features not available to earlier versions), I
can only conclude that diffs between v10 and v11 (xl2003) in this
regard were actually mistakes left in v10 that were fixed in v11. In
this regard, then, I have to be careful that what I do in xl2002 still
works in later versions. I admit to not checking this as often as I
should and so it's easy to see how folks can be misguided as a result.
(I hope my clients still using xl2002 upgrade soon<bg)

--
Garry

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