For next lus

[Amaprog74]

Beste,

onderstaande code werkt perfect voor mij maar is nogal omslachtig. Ik werk met een basisexcelblad met 15 knoppen. Echter is het niet altijd nodig om deze knoppen allemaal te gebruiken.
Vandaar dat ik in 'worksheet activate' onderstaande code heb geplaatst. Zou het mogelijk zijn om deze korter te schrijven met een for next lus? Alvast dank.

If ActiveSheet.Range("GF9").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
ElseIf ActiveSheet.Range("GF9").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Visible = True
End If

If ActiveSheet.Range("GF10").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Visible = False
ElseIf ActiveSheet.Range("GF10").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 2")).Visible = True
End If

If ActiveSheet.Range("GF11").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Visible = False
ElseIf ActiveSheet.Range("GF11").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 3")).Visible = True
End If

If ActiveSheet.Range("GF12").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Visible = False
ElseIf ActiveSheet.Range("GF12").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 4")).Visible = True
End If

If ActiveSheet.Range("GF13").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 5")).Visible = False
ElseIf ActiveSheet.Range("GF13").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 5")).Visible = True
End If

If ActiveSheet.Range("GF14").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 6")).Visible = False
ElseIf ActiveSheet.Range("GF14").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 6")).Visible = True
End If

If ActiveSheet.Range("GF15").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 7")).Visible = False
ElseIf ActiveSheet.Range("GF15").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 7")).Visible = True
End If

If ActiveSheet.Range("GF16").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 8")).Visible = False
ElseIf ActiveSheet.Range("GF16").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 8")).Visible = True
End If

If ActiveSheet.Range("GF17").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 9")).Visible = False
ElseIf ActiveSheet.Range("GF17").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 9")).Visible = True
End If

If ActiveSheet.Range("GF18").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 10")).Visible = False
ElseIf ActiveSheet.Range("GF18").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 10")).Visible = True
End If

If ActiveSheet.Range("GF19").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 11")).Visible = False
ElseIf ActiveSheet.Range("GF19").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 11")).Visible = True
End If

If ActiveSheet.Range("GF20").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 12")).Visible = False
ElseIf ActiveSheet.Range("GF20").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 12")).Visible = True
End If

If ActiveSheet.Range("GF21").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 13")).Visible = False
ElseIf ActiveSheet.Range("GF21").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 13")).Visible = True
End If

If ActiveSheet.Range("GF22").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 14")).Visible = False
ElseIf ActiveSheet.Range("GF22").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 14")).Visible = True
End If

If ActiveSheet.Range("GF23").Value = 0 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 15")).Visible = False
ElseIf ActiveSheet.Range("GF23").Value = 1 Then
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 15")).Visible = True
End If

 

[edmoor]

Zonder een voorbeeld document:

Private Sub Worksheet_Activate()
    For i = 9 To 23
         ActiveSheet.Shapes.Range(Array("Rounded Rectangle " & i - 8)).Visible = IIf(ActiveSheet.Range("GF" & i).Value = 0, False, True)
    Next i
End Sub

 

[snb]

Private Sub Worksheet_Activate()
  For j = 9 To 23
    Shapes("Rounded Rectangle " & j - 8).Visible = cells(j,188)
  Next
End Sub

 

[Amaprog74]

Dag Edmoor en SNB

Beiden bedankt voor jullie zeer verdienstelijke poging. Ik heb beiden getest en beiden werken!
Jullie hebben mij hiermee superblij gemaakt van 75 regels code naar 3 regels met hetzelfde resultaat.

Thanks thanks thanks...