Définir une autre feuil dans une variable Set

Le 10/12/2019 à 16:14

Bonjour à tous,

Je débute en VBA (c'est génial, mais je m'y perds un peu)

J'ai un fichier Excel dans lequel je mets à jour la source d'une liste déroulante en automatique.

J'ai récupéré (et à peu près compris) un code qui fonctionne très bien (merci à l'auteur dont je n'ai malheureusement pas retrouvé le nom) :

Option Explicit

Dim cell, rep

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Not Intersect(Target, Range("A:A")) Is Nothing Then

If Target <> "" Then

Set cell = Range("P2:P" & Range("P" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole)

If cell Is Nothing Then

rep = MsgBox("Cette personne n'existe pas dans la liste." & Chr(13) & _

"Voulez-vous la créer ?", 36)

If rep = 6 Then

Range("P" & Rows.Count).End(xlUp)(2) = Target

End If

End If

End If

End If

Application.EnableEvents = True

End Sub

Mon problème se situe là : "Set cell = Range("P2:P" & Range("P" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole)"

Je voudrais changer ma liste source de page, mais je ne comprends pas comment on définie dans la variable le nom de la feuille.

Ma source ira dans la feuil2.

J'ai essayé ça :

Set sheet("feuil2") = range etc..., mais j'ai un message d'erreur

Quelqu'un pourrait-il m'aider s'il vous plait ?

Est-ce qu'il faut déclarer sheet dans les options explicit ? (j'avoue, je ne sais toujours pas à quoi servent ses options explicit,mais quand j'aurai plus de temps, j'irai décortiquer ça :$)

Merci d'avance pour votre aide

Le 10/12/2019 à 16:29

Bonjour et bienvenue,

Pour spécifier la feuille , ajoute ceci

Set cell = sheets("mafeuille").Range("P2:P" & sheets("mafeuille").Range("P" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole)

Quelques conseils :

  • utilise la balise code dans tes posts pour améliorer la lisibilité comme je viens de le faire
  • indente ton code
  • joins un fichier même simplifié (en l’occurrence, dans un code de feuille je ne suis pas certain que tu puisses agir sur une autre feuille, à tester)
Le 10/12/2019 à 16:48

Merci Steelson,

Heu, je vais paraitre idiot, je ne sais pas activer les balises

J'ai testé mais j'ai un message d'erreur aussi

Je joins le fichier simplifié.

5maj-liste-deroulante-depuis-destination.xlsm (24.66 Ko)

Est-ce qu'il vaudrait mieux le mettre dans thisworbook ? (je vais essayer)

Le 10/12/2019 à 17:21

Bidule créé ...

Option Explicit

Dim cell, rep

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        If Target <> "" Then
            Set cell = Sheets("Feuil2").Range("P2:P" & Sheets("Feuil2").Range("P" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole)
            If cell Is Nothing Then
                rep = MsgBox("Cette personne n'existe pas dans la liste." & Chr(13) & _
                        "Voulez-vous la créer ?", 36)
                If rep = 6 Then
                    Sheets("Feuil2").Range("P" & Rows.Count).End(xlUp)(2) = Target
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub
3maj-liste-deroulante-depuis-destination.xlsm (24.38 Ko)
Le 10/12/2019 à 17:23
HubertM a écrit :

Merci Steelson,

Heu, je vais paraitre idiot, je ne sais pas activer les balises

Tu sélectionnes ton code et tu cliques sur 
</>
juste au-dessus du rectangle de réponse.

Il serait surtout idiot de ne pas poser de questions !

Le 10/12/2019 à 17:39

Salut Hubert,

Salut Steelson,

- pour afficher un code dans ton message, clique sur la balise "</>" dans la barre d'outils au-dessus de la fenêtre d'édition du message et colle le code entre les deux ;

  • Option Explicit t'oblige à déclarer chaque variable utilisée dans une Sub. Déclarées DANS une Sub, ces variables ne seront visibles QUE dans cette Sub et s'évanouiront dans l'éther en sortie de Sub ;
  • si tu déclares des variables juste sous 'Option Explicit', donc en dehors d'une Sub, ce seront des variables "visibles" dans tout le module et permanentes ;
  • regarde ci-dessous comment je déclare les Sheet et Range : perso, je donne TOUJOURS le même genre de nom pour chaque type de variable. Quand tu y reviens, parfois après plusieurs semaines ou mois, tu retombes facilement sur tes pattes ;
  • perso, je préfixe les Integer d'un "i" accompagné d'un nom aussi court et clair que possible ex : iRow = n° ligne), les String d'un "s", les Range d'un "r" (rCel pour une cellule, rCells pour une plage) et autres trucs...
  • quand tu te réfères à une autre feuille que la feuille active et en fonction du nombre d'instructions qui suivront, utilise WITH.
Dans ce cas, dans le bloc d'instruction, préfixe chaque objet de cette feuille d'un "." ;
Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim sWk As Worksheet, rCel As Range, iRep%
Set sWk = Worksheets("BDD")
'
Application.EnableEvents = False
'
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target <> "" Then
        With sWk
            Set rCel = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlNext)
            If rCel Is Nothing Then
                iRep = MsgBox("Cette personne n'existe pas dans la liste." & Chr(10) & "Voulez-vous la créer ?", vbQuestion + vbYesNo)
                If iRep = 6 Then .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1).Value = Target
            End If
        End With
    End If
End If
'
Application.EnableEvents = True
'
End Sub

Tu as raison : VBA est génial!

A+

2hubertm.xlsm (23.89 Ko)
Le 10/12/2019 à 18:09

Merci à vous deux,

C'est super. Quand j'aurai un niveau en VBA (un jour) je viendrai aider les autres

Merci pour les explications (simples et claires)

Steelson, je sais pourquoi ça n'a pas fonctionné (j'ai oublié un "mafeuille")

Je ne sais pas combien de fois je suis capable de vous remercier, mais en tout cas merci, merci, merci, merci, merci, merci, merci, merci, merci, merci, merci, .....

Le 10/12/2019 à 18:29

Curulis,

J'ai d'abord eu un message d'erreur

[quote][/If iRep = 6 Then .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1).Value = Targetquote]

Utilisation incorrecte de la propriété.

J'ai isolé les lignes une par une, j'ai ajouté un end if et décalé les end if et c'est tout bon :

Private Sub Worksheet_Change(ByVal Target As Range)
'
Dim sWk As Worksheet, rCel As Range, iRep%
Set sWk = Worksheets("BDD")
'
Application.EnableEvents = False
'
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    If Target <> "" Then
        With sWk
            Set rCel = .Range("P2:P" & .Range("P" & Rows.Count).End(xlUp).Row).Find(Target, lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlNext)
            If rCel Is Nothing Then
                iRep = MsgBox("Cette personne n'existe pas dans la liste." & Chr(10) & "Voulez-vous la créer ?", vbQuestion + vbYesNo)
                If iRep = 6 Then .Range("A" & .Range("A" & Rows.Count).End(xlUp).Row + 1).Value = Target
            End If

'
Application.EnableEvents = True
'
            End With
    End If
End If
End Sub

Grace à vous deux j'ai deux codes qui fonctionnent

Encore merci

Rechercher des sujets similaires à "definir feuil variable set"