Sparklines

[dpotta]

Longshot:

This is a screen shot of part of a dynamic dashboard. Is there a method, VBA or otherwise for hiding the sparkline if the value in the cell to its left is zero?

Many thanks

D

 

[RickXL]

Hi,

The picture didn't make it. However, something like this might work:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    On Error GoTo err
    If Not Intersect(Target, Columns("I:I")) Is Nothing Then
        If Target = 0 Then
            Target.Offset(0, 1).SparklineGroups.Clear
        Else
            Target.Offset(0, 1).SparklineGroups.Add Type:=xlSparkLine, _
                SourceData:=Target.Offset(0, -2).Resize(1, 3).Address
        End If
    End If
err:
    Application.EnableEvents = True
End Sub

I have assumed the possible zero value will be in column I and that the sparklines data will be in columns G to I and the sparklines in J.
If the value in column I is changed then it inserts sparklines if the value is non-zero and deletes them otherwise.