Insertion ligne excel sous condition Résolu/Fermé

Question

Bonjour à tous,

j'ai créer un formulaire avec vba qui rempli un tableau excel. je voudrai mettre des conditions et c'est là que je bloque.


voici les différentes conditions:

_ si le nom de la personne n'existe pas dans mon tableau excel, j'insère directement ma ligne.
_ si le nom de la personne existe, je compare la date du jour avec celle qui se trouve dans la ligne de la personne concernée
                         - j'aimerai remplacé par les nouveaux critères saisies dans mon formulaire si cette date est inférieur à la date du jour
                         - autrement j'affiche un message me disant que la ligne n'est pas modifiable.


meric de votre aide

Vaucluse · Answer

Bonjour
A mon avis, vous devriez utiliser la fonction RECHERCHEV (ou H) pour sur deux cellules sélectionnées spécialement de façon à:
rechercher le nom existant éventuellemnt dans la liste
Rechercher la date correspondante
Ensuite, créer une ligne d'enregistrement conditionnelle utilisant la fonction "SI" sur toute la ligne à insérer, en fonction dceu résultat précédent. 
Libérer les cellules de la ligne à insérer si message OK
Enregistrer votre ligne après modification éventuelle.
J'ai quelque chose qui marche selon ce que vous souhaitez pour l'enregistrement ds coordonnées de mes corrspondants
Pour définir tout cela, c'est difficile sans votre fichier. 
Si vous le mettez à disposition, nous pourrions essayer de le traiter.
Mais vous pouvez aussi rechercher les fonctions qui vont bien :
pour cela, dans la barre d'outil / Insertion / Fonction et vous vous laissez guider(voyez avec RECHERCHE ou INDEX. Ensuite, une macro avec l'enregistreur , à lancer lorsque la ligne est modifiée et le tour est joué.... ou presque

Bonne chance.

Science sans conscience n'est que ruine de l'Ame

aurel51 · Answer

j'avoue que je seche un peu.

je vais essayé de me reconcentrer là dessus en fin de journée.

je mets mon fichier sur le lien suivant: https://www.cjoint.com/?jskhEYaGjq

aurel51 · Answer

personne ne peut m'aider.

please

aurel51 · Answer

Voila ce que j'ai réussi à faire


Private Sub CommandButton3_Click()

Dim Var As String
On Error Resume Next
Var = InputBox("Vérifier si le nom est repertorié", , ".........")
'pour ne rien supprimer en cas d' ECHAP ou D'ANNULER
If Var = "" Then Exit Sub
Set mottrouvé = Cells.Find(What:=Var)
If Not mottrouvé Is Nothing Then
mottrouvé.Select
Range(Selection, Cells(5)).Select
If mottrouvé > Format(Date, "dd/mm/yyyy") Then
'faire exception de renouvelement d'article
Style = vbYesNo + vbDefaultButton1
Msg = "Voulez vous faire une exception ?"
Title = "Cette personne ne peut renouveler l'article"
Réponse = MsgBox(Msg, Style, Title)
If Réponse = vbYes Then
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp
UserForm1.Show

End If
End If
Else
MsgBox "Le nom n'est pas répertorié. Ouverture du formulaire de saisies"

UserForm1.Show

'Macro enregistrée le 24/09/2007 par Stagiaire
    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    Rows("7:7").Select
    Selection.Copy
    Rows("6:6").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("E6").Select
    ActiveCell.FormulaR1C1 = ""
    Range("E7").Select
    Selection.AutoFill Destination:=Range("E6:E7"), Type:=xlFillDefault
    Range("E6:E7").Select
    
End If

End Sub




maintenant j'ai des difficultés pour comparez la date qui ce trouve dans la meme ligne que le mot que j'ai recherché avec la date du jour.

j'ai aussi un problème pour que mon formulaire ne me modifie que la ligne selectionné

merci de votre aide

eriiic · Answer

Bonsoir,

Ca serait mieux si tu indiquais vers quelle ligne tu rencontres le problème...

Pour comparer la date tu ne dois pas en être loin puisque à-priori tu l'as trouvée et sélectionnée.
Mais j'ai l'impression que tu confonds cellule active et cellule selectionnée.
Tu as .select et c'est différent de .activate (regarde l'aide si besion)
insere msgbox(activecell.adress) (qui te retourne l'adresse de la cellule active) juste avant "ActiveCell.EntireRow.Select" vers la fin et tu comprendras une de tes erreurs.

La date maintenant
Tu n'es pas obligé de faire un .select dès qu'une cellule t'interesse.
L'enregistreur de macro détaille tout (trop) et c'est à toi d'alléger un peu le code pour faciliter la lecture
A partir d'une cellule tu as .offset(row,column) qui te permet de t'adresser à une cellule décalée de 'row' ligne et 'column' colonnes par rapport à une reférence (range).
Donc si mottrouvé est ok tu fais
If mottrouvé.offset(0,4).value > Format(Date, "dd/mm/yyyy") Then 'même ligne et 4 colonnes à droite
.offset() accepte les offset négatifs tant que tu ne sors pas des limites de la feuille
et tu oublies tout tes .select inutiles dans ce cas. 

Par contre les .value (et .text) sont mieux pour la lecture, même si excel te retourne le .value par défaut écris le quand même, c'est plus clair


Autre point:
Souvent tu peux appliquer une methode sans selectionner ta cellule.
exemple :
tu ecris
ActiveCell.EntireRow.Select
Selection.Delete Shift:=xlUp

fais directement ActiveCell.EntireRow.delete


Et quand tu as un soucis sur une macro, n'hésite pas à poser un point d'arret et suivre en pas à pas par F8 en regardant à chaque ligne l'évolution sur ta feuille, et en posant des espions pour voir la valeur de tes variables.

J'espère que tu progresseras avec ces explications même si je te laisse chercher un peu mais n'hésite pas à demander si besoin.
eric

aurel51 · Answer

salut,

merci beaucoup eriiic de t'interesser à mon problème. j'ai retourné le problème dans tout les sens mais je n'arrive pas à comparer ces 2 dates

If Not mottrouvé Is Nothing Then
If mottrouvé.Offset(0, 4).Value > Format(Date, "dd/mm/yyyy") Then
'faire exception de renouvelement d'article
Style = vbYesNo + vbDefaultButton1
Msg = "Voulez vous faire une exception ?"

au lieu d'afficher la question ci-dessus, le programme me renvoi directement à end sub.
Que faire?

eriiic · Answer

Bonjour aurel51

"le programme me renvoi directement à end sub"   
tu l'as fait en pas à pas pour dire qu'il va DIRECTEMENT à end sub ?

Met un point d'arret (break) et F8 dans la fenetre vba pour lui faire executer ligne à ligne, comme ça tu vois exactement par où il passe et si les tests (if...) sont corrects et correspondent bien à ce que tu veux.

Pour poser un break tu cliques dans la marge gauche (zone grise) de ton code, au niveau de la ligne où tu désires qu'il s'arrete

bon courage
eric

aurel51 · Answer

Bonjour,

en effet je me suis trompé, ça va directement à end if  après If mottrouvé.Offset(0, 4).Value > Format(Date, "dd/mm/yyyy") Then .

la question n'apparait pas. peut être mon end if n'est pas bien placé, j'ai essayé de le changé de place mais rien n'y fait.


If Not mottrouvé Is Nothing Then
If mottrouvé.Offset(0, 5).Value > Format(Date, "dd/mm/yyyy") Then
'faire exception de renouvelement d'article
Style = vbYesNo + vbDefaultButton1
Msg = "Voulez vous faire une exception ?"
Title = "Cette personne ne peut renouveler l'article"
Réponse = MsgBox(Msg, Style, Title)
If Réponse = vbYes Then
ActiveCell.EntireRow.Delete
UserForm1.Show
Else
UserForm1.Show
End If
End If

Else
MsgBox "Le nom n'est pas répertorié. Ouverture du formulaire de saisies"

UserForm1.Show

eriiic · Answer

re,

Si ton tableau n'a pas changé c'est bien en "mottrouvé.Offset(0, 4).Value" que tu trouves ta date.
Ton endif est peut-être bien placé mais c'est ton test qui est négatif pour aller directement au end if.

If mottrouvé.Offset(0, 4).Value > Format(Date, "dd/mm/yyyy") Then 
si tu as laissé 17/09/2009  en E6    ton test doit être positif et tu dois bien réaliser tout jusqu'au endif.

Si tu ne sais pas utiliser les espions va dans vba menu "Affichage / Fenetre espions" si tu n'as pas cette fenetre à l'écran
sélectionne 'mottrouvé.Offset(0, 4).Value' et fais glisser dans cette fenetre,
sélectionne  Format(Date, "dd/mm/yyyy") et fais glisser dans cette fenetre,

maintenant en pas à pas tu peux voir exactement la valeur des 2 variables que tu compares avec > dans le if
si tu as bien if 19/09/2009 > 25/09/2007 il doit executer ce qu'il y a après le then. S'il ne le fait tjs pas dans ces conditions c'est que tu compares 2 expressions non comparables. Par exemple chaine et chiffre et excel a mal converti ta date.Tu peux forcer une conversion en date avec cdate('mottrouvé.Offset(0, 4).Value) par exemple...

Je préfère te donner des idées et des moyens pour trouver le pourquoi, à toi de jouer.
Mais si tu es critique en temps je serais plus précis ce soir si tu veux (selon mon temps dispo bien sûr)
eric

aurel51 · Answer

merci beaucoup de prendre du temps pour m'aider à résoudre mes problèmes. 

j'ai fais comme tu m'as dit, avec un espion et j'ai vu qu'il ne connaissait pas la valeur de ma cellule donc j'ai mis cdate('mottrouvé.Offset(0, 4).Value) comme tu me l'avais conseillé. maintenant cela fonctionne.

j'aimerai aussi améliorer le début de mon programme:
Dim Var As String 
On Error Resume Next 
Var = InputBox("Vérifier si le nom est repertorié", , ".........") 

la recherche n'est pas très efficace et recherche sur toute la feuille.
je voudrais faire une recherche sur la 1ère colonne de mon tableau pour le nom et en même temps sur la 2ème pour le prenom.

eriiic · Answer

Vu la taille de ton tableau et la vitesse des micros je ne pense pas que tu puisses améliorer sensiblement la vitesse.
Si tu veux optimiser une recherche effectivement il faut sélectionner une zone pour appliquer ton .find
pour une colonne:  [A:A].find  (ou toute autre façon de désigner une zone du moment que tu obtiens un objet range)
ou une zone plus précise: [A6:A16] ou une union de zones: Union([A:A],[B8:F56])

Rechercher sur 2 colonnes:
possible mais ça serait compliquer les choses, autant se ramener à un problème plus simple et se fabriquer une clé unique par enregistrement.
Ajouter une colonne (qui peut être masquée) et la clé pourrait être "nom"&"prenom".
Et tu n'es pas à l'abri que 2 personnes portent les mêmes nom et prénom donc le mieux est d'utiliser l'identifiant de l'employé s'il existe.
Si tu es dans les améliorations par là tu devrais te méfier des accents selon la saisie des gens et mettre au minimum un message demandant de ne pas les saisir.

Rien à voir mais pré-remplir un champ avec ....... n'est pas dans les habitudes et surprend (bon, 1/2s seulement d'accord)
A l'occasion, si tu as d'autres questions, ça serait bien que tu déposes ta nouvelle version
Il y avait une grosse erreur dont tu n'as pas parlé et je me demande si elle y est encore ;-)
eric

aurel51 · Answer

voila je mets mon code afin que d'autres puisse s'en inspirer

Private Sub CommandButton3_Click()

Dim Var As String
On Error Resume Next
Var = InputBox("Vérifier si le nom est repertorié. Eviter les accents", , "")
'pour ne rien supprimer en cas d' ECHAP ou D'ANNULER
If Var = "" Then Exit Sub
Set mottrouvé = [A:A].Find(What:=Var)
If Not mottrouvé Is Nothing Then
If CDate(mottrouvé.Offset(0, 4).Value) > Format(Date, "dd/mm/yyyy") Then
'faire exception de renouvelement d'article
Style = vbYesNo + vbDefaultButton1
Msg = "Voulez vous faire une exception ?"
Title = "Cette personne ne peut renouveler l'article"
Réponse = MsgBox(Msg, Style, Title)
If Réponse = vbYes Then
mottrouvé.EntireRow.Delete
GoTo macro

End If

Else
'confirmer le renouvelement
Style = vbYesNo + vbDefaultButton1
Msg = "Voulez vous faire un renouvelement ?"
Title = "Renouvelement d'un article"
Réponse = MsgBox(Msg, Style, Title)
If Réponse = vbYes Then
mottrouvé.EntireRow.Delete
MsgBox "Le renouvelement est possible. Ouverture du formulaire de saisies"

macro:
UserForm1.Show
'Macro enregistrée le 24/09/2007 par Stagiaire
    Rows("6:6").Select
    Selection.Insert Shift:=xlDown
    Rows("7:7").Select
    Selection.Copy
    Rows("6:6").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("E6").Select
    ActiveCell.FormulaR1C1 = ""
    Range("E7").Select
    Selection.AutoFill Destination:=Range("E6:E7"), Type:=xlFillDefault
End If
End If
End If

End Sub


j'ai juste un petit problème sur la ligne en gras. si le nom n'existe pas dans mon tableau, cela me renvoi directement sur end if. j'aimerai que cela me renvoi sur la ligne macro:
j'ai essayé avec un goto mais cela ne fonctionne pas.

eriiic · Answer

Bonjour,

Je te déconseille fortement d'utiliser goto, tu peux toujours trouver une autre solution. C'est une solution de facilité qui rend la lecture ainsi que la recherche d'erreur difficile.
Le plus souvent c'est plus dû à un problème de structure de ton programme.
D'ailleurs peut-être le fais-tu mais ça ne se voit pas ici, il faut indenter le listing de ton programme à chaque test ou structure de boucle et désindenter à la fin de cette portion de code. C'est bcp plus facile à suivre lorsque tu dois corriger
ex
sub test()
     if a= true then
          ...
          ...
     else
          ...
     end if
end sub

si a=false tu vois tout de suite où te rendre

pareil avec :
for a=1 to n
     ....
next a

do
     ....
loop while

etc 

Fais-le et la solution à ton pb apparaitra plus clairement.

2 indices: 
- 1 "end if" mal placé
- il faut se servir de "Réponse = vbYes"eric

aurel51 · Answer

slt,

voila moi code fonctionne parfaitement bien. encore merci eric.
j'ai encore une question, lorsque je rentre une date dans mon formulaire (dans mon textbox) j'aimerai que la personne n'est pas le choix de syntaxe. j'aimerai que le format soit jj/mm/aa (27/09/07 pour aujourdhui).
comment je pourrai faire ?

merci

eriiic · Answer

Ben tu avances, c'est bien
A-priori pas de propriété masque de saisie dans un textbox (mais j'ai regardé vite fait, qcq'un nous donnera peut etre la réponse).
Dans ce cas c'est à toi de controler la saisie (dans l'evenement change du textbox par exemple).
Sinon une autre solution plus simple et qui en plus fait plus beau, plus pro etc c'est d'utiliser un contrôle calendrier.
Si tu ne l'as pas :
En mode dessin de ton formulaire clic-droit dans la boite à outils des contrôles et "controles supplementaires..."
Dans la liste tu recherches et coche "calendar control 11.0" (ou un autre car il peut y en avoir plusieurs si tu en as ajouté)
Vois si c'est adapté à ton pb.
eric

aurel51 · Answer

oui j'ai trouvé dans la liste des options de la boite à outils e calendrier mais il ne s'appelle "calendar control" mais "DTPicker".

le problème est qu'il n'y a pas de controlsource pour l'affecter à une cellule.

eriiic · Answer

J'ai eu du mal à le retrouver mais ça y est (la prochaine fois donne son nom complet de la liste déroulante grrrr)
Impecable c'est ce qu'il te faut

Et en quoi est-ce un problème ?
Tu commences à connaitre un peu les objets...
Tu sais qu'ils ont des propriétés,
tu sais qu'ils ont des methodes et des evenements,
et tu sais mettre une valeur dans une cellule (même si tu n'utilises pas la methode la plus courte ;-) )

D'ailleurs pour voir les evenements gérés par un objet (si c'est sur une feuille tu te mets en mode construction (un outil avec une equerre et une regle)), tu le selectionnes (des poignées de redimensionnement apparaissent autour) et tu double-cliques dessus.
VBA t'ouvre la bonne fenetre de code. Dans la liste déroulante en haut à droite tu as tous les evenements de l'objet et tu peux changer l'evenement sur lequel tu désires travailler.

eric

aurel51 · Answer

slt,

d'habitude je lie ma cellule avec controlsource et cela n'existe pas avec DTPicker. j'ai plein de boulot en ce moment et n'ai pas le temps de chercher une autre solution.

merci pour tous les conseils que tu m'as donné

eriiic · Answer

Bonjour,

D'accord, alors essaie qcq chose comme [Feuil1!A1].value = DTPicker1.value en adaptant nom de feuille, cellule, nom de ton contrôle

eric

aurel51 · Answer

Désolé j'avais plein de boulot. 

merci de ton aide, ca fonctionne très bien

A+

Insertion ligne excel sous condition

20 réponses

Discussions similaires