Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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 "Doms Mom" <Doms wrote in message ... |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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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" <Doms wrote in message ... 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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" wrote: 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" <Doms wrote in message ... 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? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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" wrote in message ... 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" wrote: 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" <Doms wrote in message ... 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? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
center autoshapes within cell???
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!!! "Peter T" wrote: 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" wrote in message ... 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" wrote: 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" <Doms wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell won't center contents | Excel Discussion (Misc queries) | |||
Any way to center a graphic in a particular cell? | Excel Discussion (Misc queries) | |||
cell will not center | Excel Discussion (Misc queries) | |||
Is there a keyboard shortcut to center data in a cell? | Excel Worksheet Functions | |||
Center Across Selection Vertically Help. I am trying to center te. | Excel Discussion (Misc queries) |