center autoshapes within cell???

[Doms Mom]

I would like to center an autoshape both vertically and horizontally within a
cell. None of the options under Draw, Align/Distribute are available whether
or not the autoshape is selected. Any suggestions?

 

[Jim Rech]

The tools you're looking at are disabled because they are intended to be
used with two or more shapes to, say, align their left sides, etc.

You might try this macro instead to center shapes in their cells. Select
one or more shapes (Ctrl-click) and run it:

Sub CenterSelectedShapes()
Dim Sh As Shape
For Each Sh In Selection.ShapeRange
With Sh.TopLeftCell
Sh.Left = .Left + (.Width - Sh.Width) / 2
Sh.Top = .Top + (.Height - Sh.Height) / 2
End With
Next
End Sub

--
Jim
|I would like to center an autoshape both vertically and horizontally within
a
| cell. None of the options under Draw, Align/Distribute are available
whether
| or not the autoshape is selected. Any suggestions?

 

[Peter T]

Have a go with this macro. If you are not sure what to do with it post back.

Sub CtrShpInRange()
Dim lt As Single, tp As Single
Dim rng As Range
Dim shp As Shape

'before running first
' - manually select cell or cells to centre to, then
' - manually select the shape to be centred

Set rng = ActiveWindow.RangeSelection ' previous cell selection

On Error Resume Next
Set shp = Selection.ShapeRange(1)
On Error GoTo 0

If shp Is Nothing Then
MsgBox "Select shape to be centred"
Exit Sub
End If

lt = rng.Left + (rng.Width - shp.Width) / 2
If lt < 0 Then lt = 0

tp = rng.Top + (rng.Height - shp.Height) / 2
If tp < 0 Then tp = 0

shp.Left = lt
shp.Top = tp

MsgBox shp.Name & " centred in " & rng.Address(0, 0)

End Sub

Regards,
Peter T

 

[Doms Mom]

Thanks for this. I have done plenty of macros involving formulas, but it has
been awhile and this one has me completely stumped, even with all the work
you have already done! Basically, I have 3 cells and 3 shaded boxes, one in
each cell. All are at the left margin but none are at the top of the page.
The cell is 31.28 wide. The boxes are .76 wide and high. So with a half
inch margin, left would be .5 but I cannot figure what the top would be for
each cell. (for starters).

Thanks for your assistance!

 

[Peter T]

First did you try the macro, did it work, if not were you clear what to do,
ie first select cell then select the shape then run the macro.

I only follow about half of what you describe below. left margin - does that
mean in column-A, don't need to know the dimensions, at least I don't see
why.

IF your shapes are always smaller than the cell and IF they are certain to
be located with their respective cells before you run the macro, then Jim's
macro should be good for you (though as written it will process ALL shapes
on the sheet but easily adapted).

Otherwise you need to be able to relate (to us) the shapes to their
respective cells in some way, eg by shapes name or its index to respective
cell addresses. Providing there's a way to link each shape to a cell a macro
should fall into place.

Regards,
Peter T

 

[Doms Mom]

OK Peter, I got an error message when I tried to post a reply, so this might
be a duplicate. I did get Jim's macro to run, but have two final issues.

1. I had merged columns A, B and C to make one large cell, which is what I
wanted the shape to center over, but is seems to still recognize the
individual columns and centered itself over Column C. I can live with it
being in column C, but if there is a way to get it to center across all three
columns it would be better.

2. Is there any way to set the macro to run automatically, so that if the
row height changes the shape would automatically adjust and re-center itself
in the cell? I am trying to automate this spreadsheet as much as possible
for someone who is quite likely to mess it up if he has to do too much!

Thanks for all your help!!!