Textbox margin
 

I have too questions:
1) VBA code
Selection.ShapeRange.TextFrame.MarginLeft = 0#
Selection.ShapeRange.TextFrame.MarginRight = 0#
Selection.ShapeRange.TextFrame.MarginTop = 0#
Selection.ShapeRange.TextFrame.MarginBottom = 0#
seems to don't work. I have to click off <inside Margin <checkbox
Automatic manually to get it in function

2) What is difference between
Selection.ShapeRange.TextFrame.MarginLeft = 0#
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405#, 195#, 67.5,
46.5).Select
And
Selection.ShapeRange.TextFrame.MarginLeft = 0
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405, 195, 67.5, 46.5).Select

Any assistance would be greatly appreciated.

Textbox margin
 

Try it this way:

Sub ABCD()
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
20#, 20#, 67.5, 46.5)
With shp.TextFrame
.AutoMargins = False
.MarginLeft = 0
.MarginRight = 0
.MarginBottom = 0
.MarginTop = 0
.Characters.Text = "ABCD EFGH IJKL MNOP"
End With
End Sub
--
Regards,
Tom Ogilvy


"Linda Edlund" wrote:

I have too questions:
1) VBA code
Selection.ShapeRange.TextFrame.MarginLeft = 0#
Selection.ShapeRange.TextFrame.MarginRight = 0#
Selection.ShapeRange.TextFrame.MarginTop = 0#
Selection.ShapeRange.TextFrame.MarginBottom = 0#
seems to don't work. I have to click off <inside Margin <checkbox
Automatic manually to get it in function

2) What is difference between
Selection.ShapeRange.TextFrame.MarginLeft = 0#
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405#, 195#, 67.5,
46.5).Select
And
Selection.ShapeRange.TextFrame.MarginLeft = 0
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405, 195, 67.5, 46.5).Select

Any assistance would be greatly appreciated.

Textbox margin
 

For (1): from the help documentation:
AutoMargins Property
True if Microsoft Excel automatically calculates text frame margins.
Read/write Boolean.
Remarks
When this property is True, the MarginLeft, MarginRight, MarginTop, and
MaginBottom properties are ignored.
So to make the code work add Selection.ShapeRange.TextFrame.AutoMargins=False
(this is the same as checking off that checkbox manually)

For (2):
The second set of statements is equvalent to the first and VBA will
interpret them exactly the same. Then only difference between (e.g.) 405 and
405# is that 405 is not "typed" - it could be used as an integer, long
integer, or a single or double precision floating point value in an
expression. The # sign in VBA is used to explicitly refer to a Double type
variable or number, so the second set of statements simply specifies the type
of the arguments used. But since doubles are required here, and are the only
type that makes sense, leaving the # off has no real effect; VBA still knows
you mean 405 as a Double value and can interpret it correctly without having
to use 405# to specify it.

Hope that all makes sense!
--
- K Dales


"Linda Edlund" wrote:

I have too questions:
1) VBA code
Selection.ShapeRange.TextFrame.MarginLeft = 0#
Selection.ShapeRange.TextFrame.MarginRight = 0#
Selection.ShapeRange.TextFrame.MarginTop = 0#
Selection.ShapeRange.TextFrame.MarginBottom = 0#
seems to don't work. I have to click off <inside Margin <checkbox
Automatic manually to get it in function

2) What is difference between
Selection.ShapeRange.TextFrame.MarginLeft = 0#
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405#, 195#, 67.5,
46.5).Select
And
Selection.ShapeRange.TextFrame.MarginLeft = 0
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405, 195, 67.5, 46.5).Select

Any assistance would be greatly appreciated.

Textbox margin
 

Selection.ShapeRange.TextFrame.AutoMargins=False

just for information, that didn't work for me when I tried it. I had to use
a shape rather than a ShapeRange.

--
Regards,
Tom Ogilvy


"K Dales" wrote:

For (1): from the help documentation:
AutoMargins Property
True if Microsoft Excel automatically calculates text frame margins.
Read/write Boolean.
Remarks
When this property is True, the MarginLeft, MarginRight, MarginTop, and
MaginBottom properties are ignored.
So to make the code work add Selection.ShapeRange.TextFrame.AutoMargins=False
(this is the same as checking off that checkbox manually)

For (2):
The second set of statements is equvalent to the first and VBA will
interpret them exactly the same. Then only difference between (e.g.) 405 and
405# is that 405 is not "typed" - it could be used as an integer, long
integer, or a single or double precision floating point value in an
expression. The # sign in VBA is used to explicitly refer to a Double type
variable or number, so the second set of statements simply specifies the type
of the arguments used. But since doubles are required here, and are the only
type that makes sense, leaving the # off has no real effect; VBA still knows
you mean 405 as a Double value and can interpret it correctly without having
to use 405# to specify it.

Hope that all makes sense!
--
- K Dales


"Linda Edlund" wrote:

I have too questions:
1) VBA code
Selection.ShapeRange.TextFrame.MarginLeft = 0#
Selection.ShapeRange.TextFrame.MarginRight = 0#
Selection.ShapeRange.TextFrame.MarginTop = 0#
Selection.ShapeRange.TextFrame.MarginBottom = 0#
seems to don't work. I have to click off <inside Margin <checkbox
Automatic manually to get it in function

2) What is difference between
Selection.ShapeRange.TextFrame.MarginLeft = 0#
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405#, 195#, 67.5,
46.5).Select
And
Selection.ShapeRange.TextFrame.MarginLeft = 0
or
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 405, 195, 67.5, 46.5).Select

Any assistance would be greatly appreciated.

Textbox margin
 

Thanks a lot, both of you. It works fine!

Textbox margin
 

But your code

Sub ABCD()
Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddShape(msoShapeRectangle, _
20#, 20#, 67.5, 46.5)
With shp.TextFrame
.AutoMargins = False
.MarginLeft = 0
.MarginRight = 0
.MarginBottom = 0
.MarginTop = 0
.Characters.Text = "ABCD EFGH IJKL MNOP"
End With
End Sub

works well and it's enough for me
I'm using Excel 2002
Thanks any way