Solved: Adjusting TextBox or Button Margins via VBA

[Wizard]

This has been puzzling me for some time and it has just cropped up again. Didn't find anything in the KB on this....

I have any number of instances where I add a cell comment, a text box or a button to a sheet using VBA. Setting the fonts, colors, all that good stuff is no problem UNTIL we try to set the margins.

I record a macro of setting the margins, and it gives me code like this:

daButton.ShapeRange.TextFrame.MarginLeft = 14.4
daButton.ShapeRange.TextFrame.MarginRight = 21.6
daButton.ShapeRange.TextFrame.MarginTop = 3.6
daButton.ShapeRange.TextFrame.MarginBottom = 3.6

('Selection' (as recorded) has been replaced with the object name in this example.)

But when I run the code, nothing happens. Everything else I tell it to do to the daButton object works fine... but the margins just won't budge.:banghead:

Where am I going wrong?

[Andy Pope]

You need to turn AutoMargins off before the explicit values will be used. Something not captured by the recorder.

I couldn't find the syntax to do it via the shaperange so I did it directly with the shape.


With ActiveSheet.Shapes("Text box 1").TextFrame
.AutoMargins = False
.MarginLeft = 12.76
.MarginRight = 12.76
.MarginTop = 12.19
.MarginBottom = 12.19
End With

[Wizard]

That was the clue I needed, here's what I ended up with, since I already had the button object set:


With ActiveSheet.Shapes(daButton.Name).TextFrame
.AutoMargins = FALSE
.MarginLeft = 12.76
etcetera....

You'll notice I changed the AutoMargins to False instead of True as you showed it, in accordance with your first sentence.

Thanks!!:thumb