alignment of SOME text in a textbox

[Dom]

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

 

[GS]

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.

 

[Dom]

Dom submitted this idea :








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.

 

[Gord Dibben]

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

 

[Dom]

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





- 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<CR>a new line". You can make "This is" align
left, and "a new line" align right.

 

[GS]

Dom explained :

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<CR>a 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.

 

[Gord Dibben]

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

 

[Clif McIrvin]

[ ]

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<CR>a 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(msoTextOrientationHorizontal, 153,
38.25, 150, _
144.75).Select
Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
"This is " & Chr(13) & "A new Line" & Chr(13) & ""
With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).
_
ParagraphFormat
.FirstLineIndent = 0
.Alignment = msoAlignRight
End With

HTH!

 

[Dom]

[ ]

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<CR>a 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(msoTextOrientationHorizontal, 153,
38.25, 150, _
        144.75).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "This is " & Chr(13) & "A new Line" & Chr(13) & ""
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).
_
        ParagraphFormat
      .FirstLineIndent = 0
      .Alignment = msoAlignRight
    End With

HTH!

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

 

[Clif McIrvin]

[ ]

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

 

[GS]

Dom explained on 6/17/2011 :

[ ]

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<CR>a 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(msoTextOrientationHorizontal, 153,
38.25, 150, _
        144.75).Select
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = _
        "This is " & Chr(13) & "A new Line" & Chr(13) & ""
    With Selection.ShapeRange(1).TextFrame2.TextRange.Characters(1, 9).
_
        ParagraphFormat
      .FirstLineIndent = 0
      .Alignment = msoAlignRight
    End With

HTH!

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