Search code examples
excelvbabuttoncell

Convert cell address as string to cell range

I have a cell J9 whose value is the address of other random cells, for example "$CW$178". I want then to add a shape in a cell whose address is the same as J9.Value, so in this case I would've added a circle in the $CW$178 cell. However, everytime I execute my macro I get returned Run-time error '424': Object required.

This is my code:

Sub button0_Click()
Cell2 = Range("J9").Value
Set shpOval = Shapes.AddShape(msoShapeOval, Cell2.Left, Cell2.Top, 50, 50) <--Debug highlights this line
Solution

  • Ranges need Set. Also you are missing a worksheet reference before Shapes.

    Dim visualizer As Worksheet
Set visualizer = ActiveWorkbook.Worksheets("Visualizer") ' or ThisWorkbook.

Dim Cell2 As Range
Set Cell2 = visualizer.Range(ActiveSheet.Range("J9").Value)

Dim shpOval As Shape
Set shpOval = visualizer.Shapes.AddShape(msoShapeOval, Cell2.Left, Cell2.Top, 50, 50)