How to manipulate objects using data cells in excel?

Question

Is there a way to control object's size or appearance by changing cell values linked to it? Or is it possible to link cells to object properties? Like a simple rectangle, if I'm going to change a cell value linked to its width or length, the object also updates.

garbo7441 · Accepted Answer

This would be done using a VBA Worksheet_Change event handler.  The following example will adjust the Width dimension of Rectangle 1 on every change of value in cell A1.  The following method assumes you have inserted a Rectangle on the active worksheet.

Copy the following event handler to the clipboard (highlight the entire event handler, right click inside the highlighted area, and 'Copy'):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0,0) = "A1" Then
    Shapes("Rectangle 1").Width = Target.Value
End If
End Sub

Select the worksheet containing the rectangle and right click the sheet tab at the bottom.

Select 'View Code'.

Paste the event handler into the white editing area to the right (right click inside the area and 'Paste').

Close the VBE (red button - top right).

Make an entry in A1 and press Enter and the rectangle width will be adjusted.

Note:  if you wish to adjust both dimensions using A1 for the Width and B1 for the Height, you can use the following event handler, following the method above.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "A1" Then
Shapes("Rectangle 1").Width = Target.Value
Shapes("Rectangle 1").Height = Target.Offset(0, 1).Value
ElseIf Target.Address(0, 0) = "B1" Then
Shapes("Rectangle 1").Height = Target.Value
Shapes("Rectangle 1").Width = Target.Offset(0, -1).Value
End If
End Sub

How to manipulate objects using data cells in excel?

1 Answer