Excel vba macro validation de donnée avec formule variable

Question

Salut à tous
J'ai demandé de l'aide 
à un membre, mais n'ayant pas de réponse pour le moment, je lance un appel général .
Dans un UserForm, je souhaite sélectionner une cellule afin de la désigner comme variable.
Cette variable s'insérera dans la formule de validation de donnée avec la fonction INDIRECT(). J'ai en effet préparer des noms de zones dans mon fichier se référents à des listes. 
L'objectif final de la macro est de pouvoir copier et insérer un groupe de cellules, et de définir une liste déroulante une cellule ainsi copiée. Cette liste déroulante sera fonction d'une cellule désignée comme source, elle même dépendante... (imbrication de validation de donnée avec la fonction: INDIRECT(paramètre).
J'espère avoir été clair.
Voici le Userform que j'ai crée, peux-tu me dire pourquoi cela ne fonctionne pas? Peut-être un jeu d'écriture?...
Merci infiniment d'avance.

Private Sub CommandButton2_Click()

   'sélection cellule pr plage
         Dim plage As String

        plage = UserForm2.RefEdit2.Value
        If plage = "" Then
           MsgBox "vous n'avez rien sélectionné, recommencez !"
            Exit Sub
        End If
        
     ' Insertion_compétence
        Sheets("Fiche contrat type").Select
Range("A19:N20").Select
    Selection.Copy
Sheets("Fiche contrat classe").Select
    ActiveCell.Range("A1:B1").Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Range("A1:B1").Select
    Application.CutCopyMode = False
    
 'déinition de la cellule validation
   ActiveCell.Offset(0, 1).Range("A1:B1").Select

 
 
 
 
   'définition de variable1 pr utilisation ds variable


Dim maVariable As String
        maVariable1 = plage

    'Validation donnée choix de la capacité source
   
With Selection.Validation
            .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=INDIRECT(" & maVariable1 & ")"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = "Donnée non valide"
        .InputMessage = ""
        .ErrorMessage = "! Donnée non valide !"
        .ShowInput = True
        .ShowError = True
   
    End With
   Set Cel = Nothing

 'déchargement du Userform
      Unload Me
End Sub


Private Sub CommandButton1_Click()

End Sub


Private Sub RefEdit2_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Click()

End Sub

Patrice33740 · Answer

Bonjour,

Essaies ce code :
Option Explicit

Private Sub CommandButton2_Click()

'sélection cellule pr plage
Dim cel As Range
Dim adr As String
Dim maVariable As String

Worksheets("Fiche contrat classe").Activate
adr = UserForm2.RefEdit2.Value
If adr = "" Then
  MsgBox "vous n'avez rien sélectionné, recommencez !"
  Exit Sub
ElseIf InStr(1, Replace(adr, "'", ""), "Fiche contrat classe") <> 1 Then
  MsgBox "La sélection doit être sur la feuille Fiche contrat classe, recommencez !"
  Exit Sub
End If

' Insertion_compétence
Set cel = Range(adr)
Sheets("Fiche contrat type").Range("A19:N20").Copy
cel.Insert Shift:=xlDown
Application.CutCopyMode = False

'définition de la cellule validation
Set cel = cel.Offset(0, 1)

'définition de variable pr utilisation ds variable
maVariable = "NomDeLaPlageListe"

'Validation donnée choix de la capacité source
With cel.Validation
  .Delete
  .Add Type:=xlValidateList, Formula1:="=INDIRECT(NomDeLaPlageListe)"
End With
Set cel = Nothing

'déchargement du Userform
Unload Me

End Sub

Moi49 · Answer

Merci Patrice
Pardon, mais j'ai dû modifier quelques lignes pour remettre mon code d'insertion des compétences. merci encore.
En revanche, j'ai l'impression que La formule de validation des données ne reconnais pas "=INDIRECT(NomDeLaPlageListe)"
J'ai essayé "=INDIRECT(plage)" quand la sélection du Userform était désigné "plage", et je viens d'essayer "adr"
Le débogeur ne rme propose rien quand je passe la souris sur formula.
An idea perhaps? 
Tu as dû remarquer que je ne suis pas analyste programmeur, je suis autodidacte, donc pardon si mon langage informatique est hasardeux.

Je te mets le new code:


Option Explicit

Private Sub CommandButton2_Click()

'sélection cellule pr plage
Dim cel As Range
Dim adr As String
Dim maVariable As String

Worksheets("Fiche contrat classe").Activate
adr = UserForm2.RefEdit2.Value
If adr = "" Then
  MsgBox "vous n'avez rien sélectionné, recommencez !"
  Exit Sub
ElseIf InStr(1, Replace(adr, "'", ""), "Fiche contrat classe") <> 1 Then
  MsgBox "La sélection doit être sur la feuille Fiche contrat classe, recommencez !"
  Exit Sub
End If

' Insertion_compétence
Sheets("Fiche contrat type").Select
Range("A19:N20").Select
    Selection.Copy
Sheets("Fiche contrat classe").Select
    ActiveCell.Range("A1:B1").Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Range("A1:B1").Select
    Application.CutCopyMode = False

'définition de la cellule validation
ActiveCell.Offset(0, 1).Range("A1:B1").Select

'définition de variable pr utilisation ds variable
maVariable = "NomDeLaPlageListe"

'Validation donnée choix de la capacité source
With Selection.Validation
  .Delete
  .Add Type:=xlValidateList, Formula1:="=INDIRECT(NomDeLaPlageListe)"
End With
Set cel = Nothing

'déchargement du Userform
Unload Me

End Sub

Private Sub RefEdit2_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Click()

End Sub

Merci encore, c'est déjà super que tu m'aie répondu
moi49

Patrice33740 · Answer

Il faut que NomDeLaPlageListe soit une plage nommée qui contient le nom de la plage nommée qui contient la liste des choix !

Moi49 · Answer

OK, merci, mais tu l'a compris, NomDeLaPlageListe est pour moi une variable. Je me demande si RefEdit est réellement le contrôle qu'il me faut.
En effet comme tu l'a dit, NomDeLaPlageListe doit désigner le nom de la zone contenant la liste de choix.
Or, RefEdit désigne le nom de la cellule choisie grâce au Userform.
Par quel contrôle puis-je désigner le contenu de la cellule choisie, et non son adresse?
Voici de nouveau le code modifié

Option Explicit

Private Sub CommandButton2_Click()

'sélection cellule pr plage
Dim cel As Range
Dim adr As String
Dim maVariable As String

Worksheets("Fiche contrat classe").Activate
adr = UserForm2.RefEdit2.Value
If adr = "" Then
  MsgBox "vous n'avez rien sélectionné, recommencez !"
  Exit Sub
ElseIf InStr(1, Replace(adr, "'", ""), "Fiche contrat classe") <> 1 Then
  MsgBox "La sélection doit être sur la feuille Fiche contrat classe, recommencez !"
  Exit Sub
End If

'définition de variable pr utilisation ds variable
maVariable = "RefEdit2.Value"

' Désignation "RefEdit2.Value" comme "NomDeLaPlageListe"
ActiveWorkbook.Worksheets("Fiche contrat classe").Range(adr).Name = "maVariable"
With ActiveWorkbook
.Names.Add Name:="maVariable", RefersTo:=.Worksheets("Fiche contrat classe"). _
Range(adr).Address, Visible:=False
End With

' Insertion_compétence
Sheets("Fiche contrat type").Select
Range("A19:N20").Select
    Selection.Copy
Sheets("Fiche contrat classe").Select
    ActiveCell.Range("A1:B1").Select
    Selection.Insert Shift:=xlDown
    ActiveCell.Range("A1:B1").Select
    Application.CutCopyMode = False

'définition de la cellule validation
ActiveCell.Offset(0, 1).Range("A1:B1").Select



'Validation donnée choix de la capacité source
With Selection.Validation
  .Delete
  .Add Type:=xlValidateList, Formula1:="=INDIRECT(mavariable)"
End With
Set cel = Nothing


'déchargement du Userform
Unload Me

End Sub

Private Sub RefEdit2_BeforeDragOver(Cancel As Boolean, ByVal Data As MSForms.DataObject, ByVal x As stdole.OLE_XPOS_CONTAINER, ByVal y As stdole.OLE_YPOS_CONTAINER, ByVal DragState As MSForms.fmDragState, Effect As MSForms.fmDropEffect, ByVal Shift As Integer)

End Sub

Private Sub UserForm_Click()

End Sub


Merci pour ta future réponse.
Moi 49

Moi49 · Answer

En fait, la liste déroulante ainsi créée me propose C_2_F qui est la donnée de la cellule du RefEdit.
Voila, c'est peut-être plus clair.

Patrice33740 · Answer

Bonjour,

« L'objectif final de la macro est de pouvoir copier et insérer un groupe de cellules, et de définir une liste déroulante une cellule ainsi copiée. Cette liste déroulante sera fonction d'une cellule désignée comme source, elle même dépendante... (imbrication de validation de donnée avec la fonction: INDIRECT(paramètre). » : Le code que je t'ai donné réalise ça.

J'ai du mal à comprendre ce que tu voudrais faire et sans le fichier cette discussion va devenir stérile !

Mets un exemple (non confidentiel) de ton fichier sur https://www.cjoint.com/ et copies le lien obtenu dans ton prochain message.

Tuto : Comment utiliser c.joint

 --
Cordialement
Patrice

Moi49 · Answer

OK, juste qques infos sur macros:
Le module 11 sera réduit à "Sub Creation_fiche_contrat_classe_et_numerotation()" lorsque les Userform de CREATION DE CAPACITE, COMPETENCE, SOUS COMPETENCE ET CRITERE EVALUATION  seront actifs.

Afin de lancer Userform2, il faut se placer sous le dernier bloc de compétence (par exemple A21 lors qu'on a créer une nouvelle fiche contrat.

Merci encore
N'hésite pas à me demander si j'ai omis des renseignements à te fournir.

Lien Cjoint: 	https://www.cjoint.com/c/CEAj77CgQBR

Patrice33740 · Answer

Il faut éviter les "Select" inutiles dans le code, on reprends le module 11 :
Sub Creation_fiche_contrat_classe_et_numerotation()
'
' Creation_fiche_contrat_classe_et_numerotation Macro
'

    Worksheets("Fiche contrat type").Range("A6:N22").Copy
    Range("A6").Insert Shift:=xlDown
    Application.CutCopyMode = False
    Range("F8").FormulaR1C1 = "=R[17]C+1"
    Range("A18").Select

End Sub

Sub Insertion_capacite()
'
' Insertion_capacite Macro
'

    Worksheets("Fiche contrat type").Range("A18:N20").Copy
    ActiveCell.EntireRow.Cells(1, 1).Insert Shift:=xlDown
    Application.CutCopyMode = False
    Selection.Offset(3).Select
    
End Sub
        
Sub Insertion_compétence()
'
' Insertion_compétence Macro
'

    Worksheets("Fiche contrat type").Range("A19:N20").Copy
    ActiveCell.EntireRow.Cells(1, 1).Insert Shift:=xlDown
    Application.CutCopyMode = False
    Selection.Offset(2).Select

    End Sub

Sub Insertion_sous_compétence()
'
' Insertion_sous_compétence Macro
'

    Worksheets("Fiche contrat type").Range("A20:N20").Copy
    ActiveCell.EntireRow.Cells(1, 1).Insert Shift:=xlDown
    Application.CutCopyMode = False
    Selection.Offset(1).Select

End Sub
 
Je ne comprends pas ton problème de validation, il existe déjà une validation de données dans la Fiche contrat type, si elle fonctionne, la copie fonctionnera aussi.

Moi49 · Answer

En effet, dans la fiche contrat type, la validation fonctionne pour l'ensemble des compétences (C_1.1_F à C_5.3_F). Mais ce n'est la validation attendue . 
La formule attendue de la cellule C19 est =INDIRECT($C$18)
Je te redonne donc un lien cjoint, regarde bien dans fiche contrat type.
                              https://www.cjoint.com/c/CEBv73WxyUX 
L'objectif est donc de choisir la compétence en fonction de la capacité (si capacité= C1_F, alors liste de validation compétence= C_1.1_F à C_1.4_F), (si capacité=C2_F, alors liste de validation compétence= C_2.1_F à C_2.6_F)... d'où la formule "=INDIRECT mavariable) dans la macro. Là, je ne t'apprends rien.

La difficulté est la suivante:
Lors du copiage d'une nouvelle CAPACITÉ fiche contrat type dans la fiche contrat classe, la validation de la compétence associée est appliquée sur la 1ère capacité (compétence en C22 dépendante de la capacité en C18) et ce n'est pas le résultat attendu.
Lors de l'insertion d'une nouvelle capacité, les futures compétences copiées dépendront de celle-ci.

Je dois donc choisir par le refedit2 (insertion de compétence), la capacité dont dépendra la nouvelle compétence.

Les sous-compétences devront fonctionner de la même manières, à leur tour dépendante des compétences. (si compétence= C_1.1_F, alors liste de validation sous-compétence= C_1.1.1_F à C_1.1.6_F)...
D'où le choix via un RefEdit3 futur pour les sous-compétences...

Voilà, Je peux difficilement être plus clair par le forum.
Si cela s' avérerait nécessaire, je suis prêt à communiquer sous d'autres formes, on verra...

J'ai recopier ta macro pour l'insertion des fiche contrat, c'est OK
En revanche je garde pr le moment les autres codes du module 11, car les blocs copiés doivent s'insérer d'après la position relative de la cellule sélectionnée. (Afin de lancer Userform2, il faut se placer sous le dernier bloc de compétence, par exemple A21, on choisi ainsi l'emplacement du bloc à ajouter) 

Merci de ta future réponse.

Patrice33740 · Answer

Bonjour,

Sous Excel, l'utilisation de cellules fusionnées génère de nombreux problèmes et complique sérieusement le développement des macros. 
Un bonne méthode consiste à limiter leur utilisation au strict minimum indispensable, c'est-à-dire dans la très grande majorité des cas à aucune cellule fusionnée.

Indépendamment des soucis induits par la fusion, pour résoudre le problème, le plus simple est de modifier le contrat type pour identifier le niveau (capacité, compétence, sous-compétence) des lignes insérées. Cette identification permet de trouver la valeur servant à définir la liste de validation.

Il eût été plus logique de concevoir un nouveau modèle dans cet objectif, mais pour ne pas refaire le modèle, je me suis contenté d'ajouter une colonne (et de corriger les formules de validation indirecte).

Voici un début de solution :
https://www.cjoint.com/13mi/CECwqRdGrZn__suivi_eleves_bac_pro2.xlsm

Il faut noter que de nombreux noms servant à la validation,via INDIRECT(NOM), sont mal définis (ou pas du tout), par exemples : C_1_A, C_2_F, C_5.1_F, ...

Moi49 · Answer

Bonjour Patrice
Désolé, j'ai du m'absenter du forum un moment pour le boulot.
J'ai testé ton code, c'est OK.
Merci infiniment, cela m'a beaucoup fait avancer.
Une autre question s'impose dans la poursuite de mon travail:
Lors de la distribution de la fiche contrat, je sélectionne une plage telle que:
plage = UserForm1.RefEdit1.Value
If plage = "" Then
           MsgBox "Veuillez sélectionner une fiche contrat, SVP !"
            Exit Sub
        End If
Je veux distribuer cette Sélection mais en lui ajoutant une ligne (dessous)
Peux-tu compléter ma ligne de code, please:
Range(plage).EntireRow.Select
    Selection.Copy
Je pourrais tout simplement programmer l'insertion d'une ligne, mais...
J'ai ensuite des variables me permettant de coller cette plage dans différents onglets. Ça marche.

Moi49 · Answer

Une question, concernant cette fois l'effacement d'une fiche contrat. Voici mon code: Private Sub CommandButton2_Click() 'sélection cellule pr plage Dim plage As String Dim maVariable As Range Worksheets("Fiche contrat classe").Activate plage = UserForm2.RefEdit2.Value If plage = "" Then MsgBox "vous n'avez rien sélectionné, recommencez !" Exit Sub ElseIf InStr(1, Replace(plage, "'", ""), "Fiche contrat classe") <> 1 Then MsgBox "La sélection doit se trouver sur la feuille <>, recommencez !" Exit Sub End If Range(plage).Select Selection.Resize(Selection.Rows.Count + 1).Select Selection.Rows.Delete Shift:=xlUp 'déchargement du Userform Unload Me End Sub Cela a pour effet de supprimer les cellules de la fiche sélectionnée (+ 1 ligne), mais l'objectif est de supprimer les lignes entières. Donc pour le moment, l'utilisation du Userform altère les formules de calcul des points des fiches contrat positionnées dessous, en décalant celles-ci vers le haut. J'espère avoir été clair. Merci d'avance de ta future réponse. Moi49

Patrice33740 · Answer

Bonjour,

Rappels :
1 ... sans le fichier cette discussion va devenir stérile ! 
2 il faut éviter les "Select" inutiles dans le code

D'autre part, je ne comprends pas pourquoi tu persistes dans l'utilisation des USF

Moi49 · Answer

Merci Patrice.
1: Le fichier mis à jour:
https://www.cjoint.com/?CFxqJFFp7ZG 
2: N'étant pas analyste programmeur, il est difficile pour moi de savoir ce qui est strictement nécessaire...
3: j'utilise les USF car je ne sais pas faire autrement pour certaines tâches.

Malgré cela , merci de t'intéresser à mon fichier qui compte pour moi, formalisant le suivi des élèves.
Grâce au fichier sur cjoint, peux-tu répondre aux questions sus-demandées.
J'ai résolu la question d'ajout de ligne lors de la distribution (question du 21/06)
Reste la question relative à l'effacement des fiches (question du 22/06), cas d'école pour toi.
Merci infiniment
Moi49

Patrice33740 · Answer

Bonjour,

Pour revenir sur les .Select, ils ne sont que très rarement nécessaires.
En effet, dans la très grande majorité des cas, on peut modifier un objet directement sans être obligé de le sélectionner auparavant. 
Par exemple, on peut remplacer :
Range(plage).Select
Selection.Resize(Selection.Rows.Count + 1).Select
Selection.Rows.Delete Shift:=xlUp 
par
Range(plage).Resize(plage.Rows.Count + 1).Rows.Delete Shift:=xlUp 

Avec ton fichier, j'ai fini par comprendre ce que tu appelles "critères d'évaluation", que je n'avais pas développé dans ma proposition précédente. J'ai également constaté des dysfonctionnements dans le code que je t'avais proposé pour les insertions.
 
Voici un nouveau fichier, avec la suppression de fiches où toutes les procédures ont été revues et corrigées :
https://www.cjoint.com/c/CFAomoxB55N
 
Tu devrais restructurer les feuilles «référentiel ...» de façon à pouvoir créer/mettre à jour, par macro, tous les noms indispensables  aux validations
Cordialement
Patrice

Moi49 · Answer

OK, très bien, j'essaie tout ça et je te tiens au courant sur ce forum.
Merci d'avoir pris du temps.
Cordialement,
Moi49

Bertrand · Answer

Bonjour le debut du sujet m'interresse beaucoup car j'ai soucis avec la validation de donnée,
mais je n'ai pu tiré partie de l'explication

Je voudrais, a travers une macro, créer une liste déroulante (validation de donnée) dont le contenu est dépendant de la cellule qui la précède... 

ex si la cellule A1 contient D la liste de la cellule A2 proposera D1,D2,D3....
     si la cellule A1 contient G la liste de la cellule A2 proposera G1,G2,G3....
    idem pour les cellules sous-jacentes (intérêt de la macro)

j'ai tenté les solutions ci dessous mais sans résultat,

Range("S" & d).Select
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, FormulaR1C1:="=INDIRECT(HLOOKUP(RC[-1],correscat,2,FALSE)"
        '"=INDIRECT(HLOOKUP(RC[-1],correscat,2,FALSE)"
        '="=INDIRECT(RECHERCHEH(R4;correscat;2;FAUX))"
        'ActiveCell.FormulaR1C1 = "=INDIRECT(RC[-1],correscat)"
        'ActiveCell.FormulaR1C1 = "=HLOOKUP(RC[-1],correscat,2,FALSE)"
        '"=indirect(rechercheh(R4;correscat;2;faux))"
    End With

Si qqun a une idée c'est cool!!!

Excel vba macro validation de donnée avec formule variable

17 réponses

Discussions similaires

Newsletters