Format Sheet Objects

Ktrasler · Feb 16th 2006

Hi all

I've been searching a whil but can't find how to change the format of a rectangle (object on worksheet).

I just want a simple couple of lines but can't remember how to reference the objects. eg

Code

For Each obj in .....
    obj.HorizontalAlignment = xlLeft
next

Cheers

Kev

hgus393 · Feb 16th 2006

Re: Format Sheet Objects

Does this help any?

Code

Sub DoesThisHelp()
Dim MyShape


Set MyShape = Worksheets(1)
MyShape.Shapes(1).Fill.PresetGradient _
    msoGradientHorizontal, 1, msoGradientBrass


End Sub

//Robert

Ktrasler · Feb 16th 2006

Re: Format Sheet Objects

Hi Robert

Thanks for your reply. That worked fine for one shape but needed it for all shapes.

Found out what I was doing wrong. Changing the properties like the fill colour worked fine, but if you want to change the alignment for example you have to select the shape first.

Code

Dim shp
For Each shp In ActiveSheet.Shapes
  shp.Fill.ForeColor.SchemeColor = 1
  shp.Select
    With Selection
      .HorizontalAlignment = xlCenter
    End With
Next

Cheers

Kev.

Format Sheet Objects

Participate now!

Share