Conflit entre case à cocher et liste déroulante

Résolu
Minh_Tri
Messages postés
6
Date d'inscription
lundi 8 janvier 2018
Statut
Membre
Dernière intervention
10 janvier 2018
- 8 janv. 2018 à 10:47
Minh_Tri
Messages postés
6
Date d'inscription
lundi 8 janvier 2018
Statut
Membre
Dernière intervention
10 janvier 2018
- 10 janv. 2018 à 09:03
Bonjour à tous,

Je vais essayer d'être le plus claire possible.

L'outil que je dois mettre en place sous Excel, en VBA, doit récupérer, dans une base de données externe (pas dans le même classeur et répertoire), des coûts en fonction des études et des outils que l'on choisi :
- Sur une première feuille (Projet) on retrouve des cases à cocher qui représentent les études demandées. Voici le code lié à ces cases à cocher :

Private Sub CheckBox1_Click()

'_________________________________________'

'ETUDE AVP'
'_________________________________________'

demande = "AVP"
i = "1"
MacroDemande
End Sub


"demande" est la variable qui va contenir l'étude que l'on veut. Comme le nombre d'études est grand j'ai préféré faire un code global que va reprendre chaque case à cocher. Voici le code en question :

Private Sub MacroDemande()

'__________________________________'

'AJOUT D'UNE ETUDE'
'__________________________________'

Dim ws As Worksheet

Application.ScreenUpdating = False

With Sheets("Projet")
If .OLEObjects("CheckBox" & i).Object.Value = True Then
For Each ws In ThisWorkbook.Worksheets
If ws.Range("J15").Value <> "" Then
ws.Range("P28").End(xlDown).Offset(1, 0).Value = demande
End If
Next ws
Else
For Each ws In ThisWorkbook.Worksheets
If ws.Range("J15").Value <> "" Then
Set Search = ws.Range("P28:P100").find(demande, LookIn:=xlValues, lookat:=xlWhole)
If Not Search Is Nothing Then
With ws
.Range(Search.Address, Search.Offset(0, 1).Address).Delete
.Range("P60:Q60").Copy
.Range("P61:Q61").PasteSpecial Paste:=xlPasteFormats
.Range("P60:Q60").Clear
End With
End If
End If
Next ws
End If
End With

End Sub


Ce code va permettre d'entrer l'étude cochée dans un tableau (Tableau des études) sur d'autres feuilles. Le 'copy/paste sert juste à garder la mise en forme du tableau car le 'Delete supprime la ligne et donc fait remonter la mise en forme. Le tableau est composé de 2 colonnes, en P les études et en Q le coût qui va être récupérer plutard.

- Sur la deuxième feuille (Chiffrage, qui contient le tableau décrit ci-dessus), on retrouve une barre de recherche pour saisir les outils à chiffrer, un tableau récapitulatif (Tableau des spécifiques), et une liste déroulante. On s'intéresse ici à la liste déroulante :

Private Sub ListBoxSpe_Click()

'_________________________________________'

'LISTE DES SPECIFIQUES'
'_________________________________________'

nbOP = Range("J15").Value

Application.ScreenUpdating = False

For i = 0 To ListBoxSpe.ListCount - 1
If ListBoxSpe.Selected(i) = True Then
spe = ListBoxSpe.Value 'Mémorisation de l'outil choisi dans la listbox
If spe <> "" Then
Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole)
Set OP = Sheets(ws).Rows(202).find(nbOP, lookat:=xlWhole)
Set list = Sheets(ws).Columns(1).find(spe, LookIn:=xlValues, lookat:=xlWhole) 'Recherche du spécifique dans le tableau
If Not list Is Nothing Then
Range("T30:T60").ClearContents
Range("P30").Select
Do While Not IsEmpty(ActiveCell)
demande = ActiveCell.Value 'Mémorisation de l'option
Set recherche = Range("A201:L260").find(demande, lookat:=xlPart) 'Recherche de l'option dans la bdd
If Not recherche Is Nothing Then
'Implantation des options sélectionnées pour le spécifique recherché
Range("S29").Value = spe
Range("T28").End(xlDown).Offset(1, 0).FormulaLocal = "=INDEX([BaseSource.xlsx]Feuil1!A1:K260;" & list.Row & ";" & recherche.Column & ")" & "*" & Cells(list.Row, OP.Column).Address
ActiveCell.Offset(1, 0).Select
End If
Loop

'Sélection de la ligne du spécifique dans le tableau
Cells(list.Row, OP.Column).Select

End If
Range(tableau.Address, tableau.Offset(0, 3).Address).Select
Union(Range(tableau.Address, tableau.Offset(0, 3).Address), Range(Cells(list.Row, OP.Column).Address)).Select

ElseIf Range("S29") <> "" Then
Range("S29").ClearContents
Range("T30:T60").ClearContents
End If
End If
Next i

End Sub


Elle permet dans un premier temps de visualiser les outils que l'on a saisie, en cliquant sur un outil on sélectionne la ligne où se trouve l'outil dans le tableau des spécifiques (pour ne supprimer que cet outil si on le souhaite), et enfin d'afficher le coût lié seulement à cet outil.

Pour récapituler, on choisit d'abord les études qui nous concerne (AVP, 3D, Elec... dans la première feuille "Projet"). En fonction des études qu'on à cocher, sur la feuille "Chiffrage" dans le tableau des études ne va apparaître seulement ces études. Sur cette feuille "Chiffrage" on va chercher des outils avec la barre de recherche que l'on va sélectionner (par double clique sur la cellule) pour faire apparaître les coûts d'études dans le tableau des études.
Mon problème c'est lorsque j'ai une sélection dans ma listbox (feuille "Chiffrage") et que je décoche une étude (feuille "Projet") ça m'affiche une erreur : "Erreur d'exécution '1004': Erreur définie par l'application ou par l'objet" et la ligne surlignée est la suivante :
Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole)

tableau est la variable qui me permet de trouver l'outil sélectionner dans le tableau des spécifiques afin de sélectionner toute la ligne.

Je ne comprend pas pourquoi lorsque l'on fait cette opération cela ne marche pas sachant que les 2 codes ne sont pas liés.

J'ai essayé de vous expliquer mon problème le plus clairement possible en me focalisant sur les contrôles active X concernés sans trop entrer dans les détails de mon outil Excel mais s'il reste des parties où vous êtes dans le flou n'hésitez pas à me le faire savoir ! En espérant que j'ai bien respecté les normes de demande d'aide du forum ^.^".

Il faut savoir que je suis débutant en VBA et que j'ai tout appris sur internet.

J'ai voulu illustré toute cette histoire avec des photos mais je n'ai pas réussi à insérer d'images "Request Error".

Pour les plus courageux je vous remercie d'avance et je suis à votre disposition pour toutes informations complémentaires :).

1 réponse

Patrice33740
Messages postés
8549
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
7 mai 2022
21
8 janv. 2018 à 11:20
Bonjour,

La programmation demande beaucoup de rigueur, je vois que tu ne déclares pas tes variables, c'est la première des règles à appliquer : pour cela, commences tous tes modules par Option Explicit.

Autre source potentielle d'erreur, dans le module ListBoxSpe_Click, tu utilises Range() sans préciser le feuille Tu le fais très bien dans le module MacroDemande avec With ws et .Range()
0
Minh_Tri
Messages postés
6
Date d'inscription
lundi 8 janvier 2018
Statut
Membre
Dernière intervention
10 janvier 2018

8 janv. 2018 à 11:50
Bonjour et merci pour ta réponse,
J'ai oublié de préciser que mes variables sont déclarées dans un module en public. Mais j'ai mis
Option Explicit
comme tu l'as indiqué et il a bien relevé des variables non déclarés donc je t'en remercie.
J'ai réécrit le module ListBoxSpe_Click plus correctement :

Private Sub ListBoxSpe_Click()

'_________________________________________'

'LISTE DES SPECIFIQUES'
'_________________________________________'

nbOP = Range("J15").Value

Application.ScreenUpdating = False

For i = 0 To ListBoxSpe.ListCount - 1
If ListBoxSpe.Selected(i) = True Then
spe = ListBoxSpe.Value 'Mémorisation de l'outil choisi dans la listbox
If spe <> "" Then
Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole)
Set OP = Sheets(ws).Rows(202).find(nbOP, lookat:=xlWhole)
Set list = Sheets(ws).Columns(1).find(spe, LookIn:=xlValues, lookat:=xlWhole) 'Recherche du spécifique dans le tableau
If Not list Is Nothing Then
With Sheets("Chiffrage")
.Range("T30:T60").ClearContents
.Range("P30").Select
Do While Not IsEmpty(ActiveCell)
demande = ActiveCell.Value 'Mémorisation de l'option
Set recherche = Range("A201:L260").find(demande, lookat:=xlPart) 'Recherche de l'option dans la bdd
If Not recherche Is Nothing Then
'Implantation des options sélectionnées pour le spécifique recherché
.Range("S29").Value = spe
.Range("T28").End(xlDown).Offset(1, 0).FormulaLocal = "=INDEX([BaseSource.xlsx]Feuil1!A1:K260;" & list.Row & ";" & recherche.Column & ")" & "*" & Cells(list.Row, OP.Column).Address
ActiveCell.Offset(1, 0).Select
End If
Loop

'Sélection de la ligne du spécifique dans le tableau
.Cells(list.Row, OP.Column).Select
End With
End If
With Sheets("Chiffrage")
.Range(tableau.Address, tableau.Offset(0, 3).Address).Select
Union(Range(tableau.Address, tableau.Offset(0, 3).Address), Range(Cells(list.Row, OP.Column).Address)).Select
End With
ElseIf Range("S29") <> "" Then
With Sheets("Chiffrage")
.Range("S29").ClearContents
.Range("T30:T60").ClearContents
End With
End If
End If
Next i

End Sub


Tu as bien raison de relever mon manque de rigueur, je ferai plus attention la prochaine fois, mais pour le coup ici le problème persiste :/.
0
Patrice33740
Messages postés
8549
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
7 mai 2022
21
8 janv. 2018 à 12:37
Pourquoi déclares-tu toutes tes variables publiques alors qu'un portée privée dans le module suffirait, c'est aussi une source d'erreur.
Il faut déclarer les variables avec le type ad hoc (pas toutes en variant) et la bonne portée.
0
Minh_Tri
Messages postés
6
Date d'inscription
lundi 8 janvier 2018
Statut
Membre
Dernière intervention
10 janvier 2018

8 janv. 2018 à 13:32
J'utilise plusieurs même variables dans des modules différents. Serai-t-il plus préférable de les déclarer à chaque module en privé?
Oui mes variables sont pour la plupart en Range, Integer et String.
0
Patrice33740
Messages postés
8549
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
7 mai 2022
21
Modifié le 8 janv. 2018 à 14:21
Il est préférable de les déclarer dans chaque procédure (avec Dim).
Dans ton code il n'y a aucune variables qui dusse avoir la portée publique ni même la portée privée d'un module.

Les variables publiques ne sont utiles que lorsque les modifications apportées par une procédure sont utilisées par une procédure d'un module d'un autre classeur. Tu peux limiter la portée publique au seul fichier dans lequel elles sont déclarée en utilisant Option Private Module pour limiter la portée à une procédure d'un autre module du même classeur.
Tu peux utiliser des variables privées dont la portée est limitée au module en les déclarant en tête de module (sans Public et avec ou sans Private) lorsque les modifications apportées par une procédure doivent être utilisées par une autre procédure du même module.

Il y a peu de cas où l'utilisation des variables publiques ou privées est indispensable. C'est le cas des déclarations de procédures, des constantes universelles, ...

En général, il est préférable d'utiliser une variable dont la portée est limitée à la procédure et quand c'est nécessaire, de la transmettre dans les arguments de la procédure ou de la fonction appelée.

Plus de détails sur les variables ici :
http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=51 et
http://silkyroad.developpez.com/VBA/LesVariables/
0
Patrice33740
Messages postés
8549
Date d'inscription
dimanche 13 juin 2010
Statut
Membre
Dernière intervention
7 mai 2022
21
8 janv. 2018 à 14:33
Exemple :
Option Explicit
Private Sub CheckBox1_Click()
Dim MaDemande As String
Dim Numero As String
  MaDemande = "AVP"
  Numero = "1"
  Call MacroDemande(MaDemande, Numero)
End Sub
Private Sub MacroDemande(demande As String, i As String)
Dim ws As Worksheet
  '... même code qu'avant
End Sub
0