Thread: Controlling shape formats by cell values

Hi everyone!
I am trying to develop an interactive data visualization in which the transparency of the shape could be conditioned(controlled) by the value in a cell on the worksheet. The change in the cell value must result in the change of the transparency setting for that shape. I have never used VBA, but would be willing to learn a thing of two if i must use for to get this done. However, i wondered if there is an easier way of doing this. Any help would be greatly appreciated!

Thanks in advance!

Andrey

P.S. Excel 2007

You can create a UDF (user defined function) to control the transparency of a specific shape.

In this example the result of a formula is used to alter the transparency.

Code:

Public Function UDF_TRANSPARENT(ShapeName As String, Value As Single) As Variant
    
    On Error GoTo ErrUDF
    With ActiveSheet.Shapes(ShapeName)
        .Fill.Transparency = Value
    End With
    UDF_TRANSPARENT = True
    Exit Function

ErrUDF:
    UDF_TRANSPARENT = CVErr(xlErrName)
    Exit Function
    
End Function