View shapes conditionally /E2003 and E2007

[Stefan_Sand]

hi,

I have some shapes in a column i want to show conditionally, if a ertain (...) value in another column is met.
If i want to do this shape by shape, the code would be hard to handle; i tried to do this in this way:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("k6:k15").Value = 1 Then
ActiveSheet.Shapes("G1", "G2", "G3", "G4", "G5", "G6", "G7", "G8", "G9", "G10", "G11").Visible = True
Else
ActiveSheet.Pictures("G1", "G2", "G3", "G4", "G5", "G6", "G7", "G8", "G9", "G10", "G11").Visible = False
End If
End Sub

but unfortunately, i get an error at the range statement - does anyone know, how to solve this?
stefan

[HansV]

A multi-cell range doesn't have a single value, but an array of values. What exactly are the conditions for the shapes to be visible/hidden? Do you want G1 to be hidden depending on the value of K6, etc., or do you want to hide/unhide all shapes at once?

[Stefan_Sand]

each G1 to G11 shape (in Column J) corresponds to the values in column k (in this case k6:k16),
if this works, i can make the spotlight shapes become visible or invisible by case.

[HansV]

You'll have to loop through the cells:

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim oCell As Range
  If Not Intersect(Range("K6:K16"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("K6:K16"), Target).Cells
      ActiveSheet.Shapes("G" & (oCell.Row - 5)).Visible = (oCell.Value = 1)
    Next oCell
  End If
End Sub

This is air code, I haven't actually tested it. I have assumed that you meant K6:K16 in the original version instead of K6:K15, otherwise the cells and shapes don't match.

[Stefan_Sand]

oh wow, it works in both versions,

so i can try to get with it through my spotlight dashboard