Any way to center a graphic in a particular cell?

M

metalsped

I have a small graphic that I need into insert to every applicable cell,
in a certain column. Is there any sort of way that excel can be
formatted to have this graphic be centered in each cell? It is quite
tough to keep everything centered by hand. Thanks!
 
D

Debra Dalgleish

Graphics float over cells, so they aren't affected by cell formatting.
You could use programming to centre the graphics, but there's no
built-in command that will centre them.
 
M

metalsped

Well this is for work, so it needs to be professional looking. Provided
I dont have to write an encyclopedia worth of commands for every box, I
would love to try out the program (if you know it). Thanks
 
D

Debra Dalgleish

Assuming the graphics are narrower than the columns, you could use code
similar to the following:

'====================
Sub CentreGraphics()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Left = shp.TopLeftCell.Left _
+ (shp.TopLeftCell.Width - shp.Width) / 2
Next shp
End Sub
'=====================
Well this is for work, so it needs to be professional looking. Provided
I dont have to write an encyclopedia worth of commands for every box, I
would love to try out the program (if you know it). Thanks
Click to expand...
 
M

metalsped

I feel quite silly asking, but where exactly in my excel sheet do I put
code like that? :confused: Sorry for all the questions.
 
M

metalsped

I'm halfway there. Thank you thus far Debra. It appears with tha
coding, that it is centered vertically. Is there any way to have i
centered horizontally as well
 
K

Ken Johnson

Hi metalsped,

try...

Sub CentreGraphics()
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Left = shp.TopLeftCell.Left _
+ (shp.TopLeftCell.Width - shp.Width) / 2
shp.Top = shp.TopLeftCell.Top _
+ (shp.TopLeftCell.Height - shp.Height) / 2
Next shp
End Sub

Ken Johnson
 
Top