Simplification par boucle

Version imprimable

Bonjour à tous,

J'ai un souci avec une macro, je suis débutant avec les boucles...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer, k As Integer
 
For i = 34 To 47
For k = 1 To 14
 
    If Range("B" & i & ":E" & i) <> "" Then
        ActiveSheet.Shapes("CheckBox" & k).Visible = True
    Else
        ActiveSheet.Shapes("CheckBox" & k).Visible = False
End If
Next
Next
End Sub

VBA me met une incompatibilité de type au niveau du Range. Les cellules B34:E34 sont fusionnées.
En fait j'essayais de "simplifier" le programme ci-dessous avec une boucle, bien plus pratique :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
 
If Range("B34") <> "" Then Shapes("CheckBox1").Visible = True
If Range("B34") = "" Then Shapes("CheckBox1").Visible = False
 
If Range("B35") <> "" Then Shapes("CheckBox2").Visible = True
If Range("B35") = "" Then Shapes("CheckBox2").Visible = False
 
If Range("B36") <> "" Then Shapes("CheckBox3").Visible = True
If Range("B36") = "" Then Shapes("CheckBox3").Visible = False
 
If Range("B37") <> "" Then Shapes("CheckBox4").Visible = True
If Range("B37") = "" Then Shapes("CheckBox4").Visible = False
 
If Range("B38") <> "" Then Shapes("CheckBox5").Visible = True
If Range("B38") = "" Then Shapes("CheckBox5").Visible = False
 
If Range("B39") <> "" Then Shapes("CheckBox6").Visible = True
If Range("B39") = "" Then Shapes("CheckBox6").Visible = False
 
If Range("B40") <> "" Then Shapes("CheckBox7").Visible = True
If Range("B40") = "" Then Shapes("CheckBox7").Visible = False
 
If Range("B41") <> "" Then Shapes("CheckBox8").Visible = True
If Range("B41") = "" Then Shapes("CheckBox8").Visible = False
 
If Range("B42") <> "" Then Shapes("CheckBox9").Visible = True
If Range("B42") = "" Then Shapes("CheckBox9").Visible = False
 
If Range("B43") <> "" Then Shapes("CheckBox10").Visible = True
If Range("B43") = "" Then Shapes("CheckBox10").Visible = False
 
If Range("B44") <> "" Then Shapes("CheckBox11").Visible = True
If Range("B44") = "" Then Shapes("CheckBox11").Visible = False
 
If Range("B45") <> "" Then Shapes("CheckBox12").Visible = True
If Range("B45") = "" Then Shapes("CheckBox12").Visible = False
 
If Range("B46") <> "" Then Shapes("CheckBox13").Visible = True
If Range("B46") = "" Then Shapes("CheckBox13").Visible = False
 
If Range("B47") <> "" Then Shapes("CheckBox14").Visible = True
If Range("B47") = "" Then Shapes("CheckBox14").Visible = False
End Sub

Un peu d'aide serait la bienvenue !

Merci

03/03/2017, 12h55
joe.levrai

Bonjour,

si tu veux vérifier que toutes les cellules d'une plage sont vides, tu ne peux pas tester directement la propriété .Value de la plage complète.

tu peux rassembler les valeurs des cellules en les concaténant ou en les rassemblant (Application.Join) et tester si la chaine de caractères créée est vide
sinon, tu peux compter le nombre de cellules vides et voir si c'est le nombre de cellules de ta plage (fonction Excel NB() qui est application.worksheetsfunction.count() en vba)

et bien d'autres méthodes encore ;)
03/03/2017, 13h01
Lyricslife

J'ai modifié pour ne garder que Range("B" & i) puisque c'est le nom de la cellule et là ça fonctionne mais uniquement quand toutes les cellules (B34:B47) sont non vides.
Si B46, par exemple, est vide, toutes mes CheckBox disparaissent. En fait ce que je voudrais c'est que si la cellule B46 est vide, la Checkbox13 soit invisible. Si B46 est non vide, la CheckBox13 est visible.
Si B34 est vide, CheckBox1 n'apparait pas, si B34 est non vide, Checkbox1 apparait.
Si B35 est vide, CheckBox2 n'apparait pas, si B35 est non vide, Checkbox2 apparait.
Et ainsi de suite.

Bonjour Lyricslife,

Je ne comprend pas deux choses :

Pourquoi la lettre E dans ta boucle alors que dans le code en dessous il s'agit seulement de la colonne B ?
Pourquoi deux boucles ?

Pour la seconde je n'attend pas de réponse mais je t'invite a réfléchir sur ce que fait ton code avec les deux boucles. En fait tu boucles sur tous les ComboBox pour chaque cellule
Ce n'est pas ce que fait le code en dessous.
On peut constater que numéro du ComboBox +33 est le numéro de ligne donc une seule boucle suffit ;) (la simplification avance)

Ensuite un code comme ça :
Code:

1 2 3 4 5 Si Test est VRAI alors Objet.propriete = VRAI Sinon Objet.propriete = FAUX Fin de si
peu se factoriser comme ça :

Code:

Objet.propriete = Test

Avec mes explications regarde ce code :
Code:

1 2 3 4 5 6 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Integer For i = 1 To 14 ActiveSheet.Shapes("CheckBox" & i).Visible = ActiveSheet.Cells(i + 33, 2) <> "" Next End Sub
Il reproduit exactement le code que tu cherches a simplifier.

03/03/2017, 13h10
joe.levrai

Je ne comprend pas vraiment pourquoi tu fais un test sur chaque cellule à chaque fois

tu es dans l'évènement Change, donc il se déclenche quand tu modifies une cellules

ne travaille ton test et l'action que sur cette cellule qui a fait l'objet d'une modification

il devient ainsi plus simple de faire correspondre la ligne de la cellule modifier et le CheckBox à gérer

EDIT : non seulement doublé, mais avec une explication beaucoup plus illustrée que la mienne :mrgreen:
03/03/2017, 13h45
Lyricslife

Merci beaucoup pour vos réponses !

Anthony, ton dernier code est effectivement exactement ce qu'il me fallait, mais je t'avoue que je ne le comprend pas trop. Je vais l'étudier d'un peu plus près.
Pour répondre à vos interrogations :
- la lettre E dans ma boucle c'est parce que pour passer de la valeur i = 34 à i = 35, la boucle se répétait plusieurs fois. Je m'étais donc dis que c'était à cause de la fusion des cellules B à E ;
- deux boucles parce que j'avais deux variables : i qui représente les numéro de lignes, et k qui représente les numéros de mes CheckBoxes. Mais effectivement je n'avais pas pensé à ta solution, plus simple.

Encore un grand merci à vous deux :)

EDIT : j'ai saisi ! Je pense que je n'ai pas encore tout à fait la logique VBA...