Recording a macro shape

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Recording a macro shape

Post by ABabeNChrist »

I’m trying to record a macro to insert a red circle, I can do this in word just fine but for some unknown reason it will not allow me to record this in excel.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Recording a macro shape

Post by HansV »

Try code like this:

Code: Select all

Dim shp As Shape
Set shp = ActiveSheet.Shapes.AddShape( _
  Type:=msoShapeOval, _
  Left:=ActiveCell.Left, _
  Top:=ActiveCell.Top, _
  Width:=ActiveCell.Width, _
  Height:=ActiveCell.Height)
shp.Name = "MyOval"
shp.Fill.ForeColor.RGB = vbRed
This will place an oval in the active cell.
Of course, you can specify different values for Left, Top, Width and Height.
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Recording a macro shape

Post by ABabeNChrist »

Hi HansV
is it possible to have a transparency fill and have it in front

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Recording a macro shape

Post by HansV »

With the variable shp from my previous reply, you can set the transparency as follows:

shp.Fill.Transparency = 0.4

Transparency can be between 0 (opaque) and 1 (clear).

A new shape will by default be in the foreground, but you can force it by adding a line

shp.ZOrder msoBringToFront
Best wishes,
Hans

ABabeNChrist
SilverLounger
Posts: 1868
Joined: 25 Jan 2010, 14:00
Location: Conroe, Texas

Re: Recording a macro shape

Post by ABabeNChrist »

Thank you HansV
With your assistance and a little tweaking, I was able to get just what I was hoping for (1 in red and 1 in yellow)

Code: Select all

Dim shp As shape
Set shp = ActiveSheet.Shapes.AddShape( _
  Type:=msoShapeOval, _
  Left:=ActiveCell.Left, _
  Top:=ActiveCell.Top, _
  Width:=50, _
  Height:=50)
shp.Name = "MyOval"
shp.Line.ForeColor.RGB = vbRed
shp.Fill.Transparency = 1#
And I was also able to use to create arrows in both colors

Code: Select all

Dim shp As shape
Set shp = ActiveSheet.Shapes.AddShape( _
  Type:=msoShapeUpArrow, _
  Left:=ActiveCell.Left, _
  Top:=ActiveCell.Top, _
  Width:=20, _
  Height:=50)
shp.Name = "MyArrow"
shp.Line.ForeColor.RGB = vbRed
shp.Fill.ForeColor.RGB = vbRed