Recording a macro shape

[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.

[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.

[ABabeNChrist]

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

[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

[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