Afficher / Modifier des valeurs d'une plages filtrées

Le 23/03/2015 à 09:53

Bonjour,

Je rencontre un problème lors de l'écriture d'un code en VBA sur mon fichier.

A partir d'un userform, je souhaite afficher certains détecteurs étalonnés pour emprunt.

Ce qui marche :

- Autofilter qui me permet d'avoir dans ma combobox uniquement les détecteurs étalonnés.

'Filtre les données par emprunt
Selection.AutoFilter Field:=10, Criteria1:="En stock"
'Affiche les données filtrées de la colonne A
Dim i As Integer
Me.Caption = T
    With Feuil2
        For i = 2 To .Range("A35000").End(xlUp).Row
                While Rows(i).Hidden = True
                    i = i + 1
                Wend
            Me.Constructeur.AddItem .Range("A" & i)
        Next
    End With

Ce qui ne marche pas :

  • Textbox qui n'affiche pas les bonnes valeurs (nb de jours restants avant étalonnage),
  • Enregistrement du Nom et Site de l'emprunteur correspondant au bon détecteur.

Je vous joint mon fichier pour y voir un peu plus clair.

Merci par avance.

23systeme-de-gestion-des-detecteurs-de-gaz-copie.xlsm (134.00 Ko)
Le 23/03/2015 à 10:20

Bonjor Seybi, bonjour le forum,

Ce qui ne marche pas c'est d'envoyer un fichier protégé par mot de passe !...

Le 23/03/2015 à 10:56

Milles excuses !

MDP ouverture : detecteurtvx

MDP modification : QSETVX

Le 23/03/2015 à 11:47

Bonjour Seybi, bonjour le forum,

5 Userforms, 7 modules dans ton fichier ! Où se trouve le code en question ?

Heureusement que j'ai téléchargé l'application "Tirage des vers du nez" dans mon passmartphone...

Le 23/03/2015 à 12:40

Bonjour ThauThème,

Il s'agit du userform 3.

Pour la textbox :

Private Sub Constructeur_Click()
Dim i As Integer
Dim x As Byte
i = Me.Constructeur.ListIndex + 2
    With Feuil2
        x = 13
        Me.Controls("Textbox" & x) = .Cells(i, x)
    End With
End Sub

Pour rentrer les Nom et Site de l'emprunteur :

Private Sub CommandButton1_Click()
rep = MsgBox("Voulez-vous continuer ?", vbYesNo + vbQuestion + vbDefaultButton1, "Attention")
If rep = vbYes Then
Unload Me
End If
'Enleve protection feuille
Sheets("Fichier_source").Select
ActiveSheet.Unprotect "QSETVX"
'Evite le ralentissement de VBA
Application.ScreenUpdating = False
'Oter la protection de la feuille
Sheets("Fichier_source").Select
ActiveSheet.Unprotect "QSETVX"
'Enlève filtre
ActiveSheet.Range("$A$1:$Q$65535").AutoFilter Field:=10
Dim i As Integer
Dim x As Byte
    i = Me.Constructeur.ListIndex + 2
        With Feuil2
            x = 6
             .Cells(i, x) = Me.Controls("Textbox" & x)
            x = 7
             .Cells(i, x) = Me.Controls("Textbox" & x)
        End With
'Tri de la plage de donnée "fichier_source" de A à Z
    ActiveWorkbook.Worksheets("Fichier_source").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Fichier_source").AutoFilter.Sort.SortFields.Add Key _
        :=Range("A1:A500"), SortOn:=xlSortOnValues, Order:=xlAscending, _
        DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Fichier_source").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
'Protection de la feuille
Sheets("Fichier_source").Select
ActiveSheet.Protect "QSETVX"
'Ouverture de la feuille "Applications
Sheets("Applications").Select
'Message de fin
MsgBox "Emprunt réalisé.", vbInformation
End Sub

J'espère que ce sera suffisamment clair.

Merci

Le 23/03/2015 à 14:43

Bonjour Seybi, bonjour le forum,

Désolé Seybi, mais je trouve trop pénible de travailler avec un classeur protégé où il faut rentrer les codes à chaque ouverture. Pareil pour la vision plein écran... Ça me gave ! J'estime que si tu as besoin d'aide tu dois faciliter au maximum la tâche de ceux qui sont prêts à le faire...

Le 23/03/2015 à 17:14

Bonjour ThauThème, Bonjour le forum

Désolé pour les verrou et le plein écran.

Je joins le fichier sans toutes ces petites choses casse-pied.

Encore désolé.

@+

16systeme-de-gestion-des-detecteurs-de-gaz-sans-verrou.xlsm (117.65 Ko)
Le 23/03/2015 à 23:24

Bonsoir Seybi, bonsoir le forum,

voici ton code :

Private Sub Constructeur_Click()
Dim I As Integer
Dim x As Byte
I = Me.Constructeur.ListIndex + 2
    With Feuil2
        x = 13
        Me.Controls("Textbox" & x) = .Cells(I, x)
    End With
End Sub

Pourquoi utiliser une variable X (puisqu'elle est constante) ?

Il n'y a rien dans la colonne 13. Donc .Cells(I, 13) ne renvoie rien quel que soit I...

Vu que la Label propose Nombre de jour avant étalonnage, tu devrais avoir dans la TextBox13 la différence de jours ente aujourd'hui et la date du prochain étalonnage non ?

En pièce jointe ton fichier modifié. Voilà comment je verrais les choses :

Private A As Worksheet 'déclare la variable A (onglet Applications)
Private Fs As Worksheet 'déclare la variable FS (onglet Fichier_source)

Private Sub UserForm_Initialize()

'**********************************************************
'il n 'y a plus de filtre automatique !
'Le tri ne sert à rien dans cette UserForm je l'ai supprimé
'**********************************************************

Dim TC As Variant 'déclare la variable TC (Tableau de Cellules)
Dim NL As Integer 'déclare la variable NL (Nombre de lignes)
Dim NC As Byte 'déclare la variable NC (Nombre de Colonnes)
Dim I As Integer 'déclare la variable I (Incrément de ligne)

Set A = Sheets("Applications") 'définit l'onglet A
Set Fs = Sheets("Fichier_source") 'définit l'onglet Fs
Fs.Unprotect "QSETVX" 'déprotège l'onglet Fs
TC = Fs.Range("A1").CurrentRegion 'définit le tableau de cellules TC
NL = UBound(TC, 1) 'définit le nombre de lignes NL du tableau de cellules TC
NC = UBound(TC, 2) 'définit le nombre de colonnes NC du tableau de cellules TC
For I = 2 To NL 'boucle sur toutes les ligne de TC (en partant de la seconde)
    'si la valeur ligne I colonne 10 (=>colonne J) de tc est égale à "En Stock",
    'ajoute la valeur ligne I colonne 1 de TC à la combobox [Constructeur]
    If TC(I, 10) = "En stock" Then Me.Constructeur.AddItem TC(I, 1)
Next I 'prochaine ligne de la boucle
End Sub

Private Sub Constructeur_Click()
Dim I As Integer 'déclare la variable I (Incrément de ligne)
Dim AJD As Date 'déclare la variable AJD (date d'AuJourD'hui))
Dim DPE As Date 'déclare la variable DPE (Date du Prochain Étalonnage)
Dim TDPE() As String 'déclare la variable TDPE (Tableau de la Date du Prochain Étalonnage)

AJD = DateSerial(Year(Date), Month(Date), Day(Date)) 'définit la date seriale d'aujourd'hui AJD
I = Me.Constructeur.ListIndex + 2 'définit la variable I
TDPE = Split(Fs.Cells(I, 4), "/") 'définit le tableau de la date du prochain étalonnage TDPE
DPE = DateSerial(TDPE(2), TDPE(1), TDPE(0)) 'définit la date sériale du prochain étalonnage DPE
Me.TextBox13.Value = DPE - AJD 'renvoie dans la TextBox13 le numéro de jour
End Sub

Private Sub CommandButton1_Click() 'bouton "Valider"
Dim I As Integer 'déclare la variable I (Incrément de ligne)

If Me.Constructeur.Value = "" Then 'condition : si la combobox [Constructeur] est vide
    MsgBox "Saisie OBLIGATOIRE de la référence du détecteur !", vbCritical, "Attention" 'Message
    Me.Constructeur.SetFocus 'place le curseur dans la combobox
    Exit Sub 'sort de la procédure
End If 'fin de la condition
If Me.TextBox6.Value = "" Then 'condition : si la TextBox6 est vide
    MsgBox "Saisie OBLIGATOIRE du NOM de l'emprunteur !", vbCritical, "Attention" 'Message
    Me.TextBox6.SetFocus 'place le curseur dans la textBox6
    Exit Sub 'sort de la procédure
End If 'fin de la condition
If Me.TextBox7.Value = "" Then 'condition : si la TextBox6 est vide
    MsgBox "Saisie OBLIGATOIRE du SITE de l'emprunteur !", vbCritical, "Attention"
    Me.TextBox7.SetFocus 'place le curseur dans la textBox7
    Exit Sub 'sort de la procédure
End If 'fin de la condition
I = Me.Constructeur.ListIndex + 2 'définit la variable I
Fs.Cells(I, 6).Value = Me.TextBox6.Value 'renvoie la valeur de la Textbox6 dans la cellule ligne I colonne 6 de l'onglet Fs
Fs.Cells(I, 7).Value = Me.TextBox7.Value 'renvoie la valeur de la Textbox7 dans la cellule ligne I colonne 7 de l'onglet Fs
'Fs.Protect "QSETVX"
MsgBox "Emprunt réalisé.", vbInformation 'message
Unload Me
End Sub

Private Sub CommandButton2_Click() 'bouton Annuler
'Fs.Protect "QSETVX"
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Fs.Protect "QSETVX"
End Sub
32seybi-v01.xlsm (84.45 Ko)
Le 24/03/2015 à 10:07

Bonjour ThauThème, bonjour le forum,

Je viens de lancer l'userform, malheureusement il ne fait pas le lien entre la référence du détecteur et le reste de la ligne aussi bien pour le nombre de jours avant étalonnage que pour les Nom et Site de l'emprunteur.

Pour le nombre de jours avant étalonnage:

La combobox prend bien les détecteurs "en stock" mais le textbox reprend un à un (sans passer ceux qui nous intéressent pas) le nombre de jours avant étalonnage.

Pour les Nom et Site de l'emprunteur:

Lorsque je prend un détecteur en milieu de liste et je l'emprunte à la validation il m'indique "Variable objet ou variable de bloc With non définie."

Merci

Le 24/03/2015 à 12:19

Bonjour Seybi, bonjour le forum,

Chez moi ça marche. Je choisis une référence (KA411-1005294), la TextBox13 affiche le nombre de jour qu'il reste entre aujourd'hui et la date du prochain étalonnage (158). Dans "Nom" je tape THAUTHEME, dans "Site" je tape SETE et je valide... J'ai bien le message qui signale que l'emprunt a été réalisé....

Je regarde le tableau et à la ligne 3 j'ai bien THAUTHEME en colonne F et SETE en colonne G.

Donc je ne comprends pas ce que tu veux dire par : malheureusement il ne fait pas le lien entre la référence du détecteur et le reste de la ligne aussi bien pour le nombre de jours avant étalonnage que pour les Nom et Site de l'emprunteur.

je te propose de me détailler pas à pas un exemple qui plante (comme je viens de le faire au-dessus) sinon on va pas s'en sortir...

Le 24/03/2015 à 14:17

Bonjour ThauThème, bonjour le forum,

Lorsque je choisis la référence (KA413-1125000), la TextBox13 affiche 158 au lieu de 179 jours. Dans "Nom" je tape Seybi, dans "Site" je tape PARIS et je valide... J'ai bien le message qui signale que l'emprunt a été réalisé....

Je regarde le tableau et à la ligne 60 je n'ai pas Seybi en colonne F et PARIS en colonne G.

Par contre Seybi apparait en G19 et PARIS en F19.

Le 24/03/2015 à 16:16

Bonjour Seybi, bonjour le forum,

Tu as mille fois raison et je te prie d'accepter mes excuses. Le numéro de ligne ne correspond plus à l'index de la combobox puisque il y a comme un filtre. Alors j'ai stocké le numéro de ligne dans une seconde colonne (masquée) de la combobox et le tour est joué.

Le code modifié avec, encore une fois, toutes mes excuses :

Private A As Worksheet 'déclare la variable A (onglet Applications)
Private Fs As Worksheet 'déclare la variable FS (onglet Fichier_source)
Private TC As Variant 'déclare la variable TC (Tableau de Cellules)

Private Sub UserForm_Initialize()

'**********************************************************
'il n 'y a plus de filtre automatique !
'Le tri ne sert à rien dans cette UserForm je l'ai supprimé
'**********************************************************

Dim NL As Integer 'déclare la variable NL (Nombre de lignes)
Dim NC As Byte 'déclare la variable NC (Nombre de Colonnes)
Dim I As Integer 'déclare la variable I (Incrément de ligne)

Me.Constructeur.ColumnCount = 2 'définit le nombre de colonnes de la combobox [Constructeur]
Me.Constructeur.ColumnWidths = ";0pt" 'masque la seconde colonne de la combobox [Constructeur]
Set A = Sheets("Applications") 'définit l'onglet A
Set Fs = Sheets("Fichier_source") 'définit l'onglet Fs
Fs.Unprotect "QSETVX" 'déprotège l'onglet Fs
TC = Fs.Range("A1").CurrentRegion 'définit le tableau de cellules TC
NL = UBound(TC, 1) 'définit le nombre de lignes NL du tableau de cellules TC
NC = UBound(TC, 2) 'définit le nombre de colonnes NC du tableau de cellules TC
For I = 2 To NL 'boucle sur toutes les ligne de TC (en partant de la seconde)
    'condition : si la valeur ligne I colonne 10 (=>colonne J) de tc est égale à "En Stock",
    If TC(I, 10) = "En stock" Then
        With Me.Constructeur 'prend en compte la combobox [Constructeur]
            .AddItem 'ajoute un élément
            .Column(0, .ListCount - 1) = TC(I, 1) 'récupere la référence dans la colonne 0
            .Column(1, .ListCount - 1) = I 'récupère le numéro de ligne dans la colonne 1 (masquée)
        End With 'fin de la prose en compte de la combobox [Constructeur]
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle
End Sub

Private Sub Constructeur_Click()
Dim I As Integer 'déclare la variable I (Incrément de ligne)
I = Me.Constructeur.Column(1, Me.Constructeur.ListIndex) 'définit la variable I avec le numéro de ligne dans la colonne 1 masquée
Me.TextBox13.Value = TC(I, 2) 'renvoie dans la TextBox13 le numéro de jour
End Sub

Private Sub CommandButton1_Click() 'bouton "Valider"
Dim I As Integer 'déclare la variable I (Incrément de ligne)

If Me.Constructeur.Value = "" Then 'condition : si la combobox [Constructeur] est vide
    MsgBox "Saisie OBLIGATOIRE de la référence du détecteur !", vbCritical, "Attention" 'Message
    Me.Constructeur.SetFocus 'place le curseur dans la combobox
    Exit Sub 'sort de la procédure
End If 'fin de la condition
If Me.TextBox6.Value = "" Then 'condition : si la TextBox6 est vide
    MsgBox "Saisie OBLIGATOIRE du NOM de l'emprunteur !", vbCritical, "Attention" 'Message
    Me.TextBox6.SetFocus 'place le curseur dans la textBox6
    Exit Sub 'sort de la procédure
End If 'fin de la condition
If Me.TextBox7.Value = "" Then 'condition : si la TextBox6 est vide
    MsgBox "Saisie OBLIGATOIRE du SITE de l'emprunteur !", vbCritical, "Attention"
    Me.TextBox7.SetFocus 'place le curseur dans la textBox7
    Exit Sub 'sort de la procédure
End If 'fin de la condition
I = Me.Constructeur.Column(1, Me.Constructeur.ListIndex) 'définit la variable I avec le numéro de ligne dans la colonne 1 masquée
Fs.Cells(I, 6).Value = Me.TextBox6.Value 'renvoie la valeur de la Textbox6 dans la cellule ligne I colonne 6 de l'onglet Fs
Fs.Cells(I, 7).Value = Me.TextBox7.Value 'renvoie la valeur de la Textbox7 dans la cellule ligne I colonne 7 de l'onglet Fs
'Fs.Protect "QSETVX"
MsgBox "Emprunt réalisé.", vbInformation 'message
Unload Me
End Sub

Private Sub CommandButton2_Click() 'bouton Annuler
'Fs.Protect "QSETVX"
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Fs.Protect "QSETVX"
End Sub
Le 24/03/2015 à 17:46

Bonjour ThauThème, bonjour le forum,

Ca marche bien mieux

Une dernière question, j'ai repris le code pour mon userform4. Dans Userform_Initialize, j'ai rentré :

    Private Sub UserForm_Initialize()
    Dim NL As Integer 'déclare la variable NL (Nombre de lignes)
    Dim NC As Byte 'déclare la variable NC (Nombre de Colonnes)
    Dim I As Integer 'déclare la variable I (Incrément de ligne)

    Me.Constructeur.ColumnCount = 2 'définit le nombre de colonnes de la combobox [Constructeur]
    Me.Constructeur.ColumnWidths = ";0pt" 'masque la seconde colonne de la combobox [Constructeur]
    Set A = Sheets("Applications") 'définit l'onglet A
    Set Fs = Sheets("Fichier_source") 'définit l'onglet Fs
    Fs.Unprotect "QSETVX" 'déprotège l'onglet Fs
    TC = Fs.Range("A1").CurrentRegion 'définit le tableau de cellules TC
    NL = UBound(TC, 1) 'définit le nombre de lignes NL du tableau de cellules TC
    NC = UBound(TC, 2) 'définit le nombre de colonnes NC du tableau de cellules TC
    For I = 2 To NL 'boucle sur toutes les ligne de TC (en partant de la seconde)
       'condition : si les valeurs ligne I colonne 6 ou 7 (=>colonne F ou G) de tc sont différentes de rien,
       If TC(I, 6) <> "" Or TC(I, 7) <> "" Then
            With Me.Constructeur 'prend en compte la combobox [Constructeur]
               .AddItem 'ajoute un élément
               .Column(0, .ListCount - 1) = TC(I, 1) 'récupere la référence dans la colonne 0
               .Column(1, .ListCount - 1) = I 'récupère le numéro de ligne dans la colonne 1 (masquée)
           End With 'fin de la prose en compte de la combobox [Constructeur]
       End If 'fin de la condition
    Next I 'prochaine ligne de la boucle
    End Sub

Un seul soucis, je lui demande de m'afficher les références des détecteurs pour lesquelles en colonne 6 ou 7 les cellules sont non vides mais cela ne marche pas.

Est-ce normale?

Merci

13systeme-de-gestion-des-detecteurs-de-gaz-vf.xlsm (127.50 Ko)
Le 24/03/2015 à 20:38

Bonsoir Seybi, bonsoir le forum,

Peut-être avais-tu oublié la déclaration des variables [Private] en début de module ?...

Le code modifié pour l'UserForm4 :

Private A As Worksheet 'déclare la variable A (onglet Applications)
Private Fs As Worksheet 'déclare la variable FS (onglet Fichier_source)
Private TC As Variant 'déclare la variable TC (Tableau de Cellules)

Private Sub UserForm_Initialize()

'**********************************************************
'il n 'y a plus de filtre automatique !
'Le tri ne sert à rien dans cette UserForm je l'ai supprimé
'**********************************************************

Dim NL As Integer 'déclare la variable NL (Nombre de lignes)
Dim NC As Byte 'déclare la variable NC (Nombre de Colonnes)
Dim I As Integer 'déclare la variable I (Incrément de ligne)

Me.Constructeur.ColumnCount = 2 'définit le nombre de colonnes de la combobox [Constructeur]
Me.Constructeur.ColumnWidths = ";0pt" 'masque la seconde colonne de la combobox [Constructeur]
Set A = Sheets("Applications") 'définit l'onglet A
Set Fs = Sheets("Fichier_source") 'définit l'onglet Fs
Fs.Unprotect "QSETVX" 'déprotège l'onglet Fs
TC = Fs.Range("A1").CurrentRegion 'définit le tableau de cellules TC
NL = UBound(TC, 1) 'définit le nombre de lignes NL du tableau de cellules TC
NC = UBound(TC, 2) 'définit le nombre de colonnes NC du tableau de cellules TC
For I = 2 To NL 'boucle sur toutes les ligne de TC (en partant de la seconde)
   'condition : si la valeur ligne I colonne 6 ou 7 (=>colonne F ou G) de tc est vide
    If TC(I, 6) <> "" Or TC(I, 7) <> "" Then
        With Me.Constructeur 'prend en compte la combobox [Constructeur]
            .AddItem 'ajoute un élément
            .Column(0, .ListCount - 1) = TC(I, 1) 'récupere la référence dans la colonne 0
            .Column(1, .ListCount - 1) = I 'récupère le numéro de ligne dans la colonne 1 (masquée)
        End With 'fin de la prise en compte de la combobox [Constructeur]
    End If 'fin de la condition
Next I 'prochaine ligne de la boucle
End Sub

Private Sub CommandButton1_Click()
Dim I As Integer 'déclare la variable I (Incrément de ligne)

If Me.Constructeur.Value = "" Then 'condition : si la combobox [Constructeur] est vide
   MsgBox "Saisie OBLIGATOIRE de la référence du détecteur !", vbCritical, "Attention" 'Message
   Me.Constructeur.SetFocus 'place le curseur dans la combobox
   Exit Sub 'sort de la procédure
End If 'fin de la condition
I = Me.Constructeur.Column(1, Me.Constructeur.ListIndex) 'définit la variable I avec le numéro de ligne dans la colonne 1 masquée
Fs.Cells(I, 6).Value = "" 'vide la cellule ligne I colonne 6 de l'onglet Fs
Fs.Cells(I, 7).Value = "" 'vide la cellule ligne I colonne 7 de l'onglet Fs
If OptionButton1.Value = True Then
    Fs.Cells(I, 8) = "Erreur"
End If
If OptionButton2.Value = True Then
    Fs.Cells(I, 8) = "Détérioration"
End If
If OptionButton3.Value = True Then
    Fs.Cells(I, 1) = ""
    Fs.Cells(I, 3) = ""
    Fs.Cells(I, 8) = ""
    Fs.Cells(I, 9) = ""
    Fs.Cells(I, 11) = ""
End If
'Fs.Protect "QSETVX"
'Message de fin
MsgBox "Retour ou perte enregistrée.", vbInformation
Unload Me
End Sub

Private Sub CommandButton2_Click() 'bouton Annuler
'Fs.Protect "QSETVX"
Unload Me
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Fs.Protect "QSETVX"
End Sub

Ne dépose pas, síl te plaît des pièce jointes bloquées par mot de passe...

Le 25/03/2015 à 09:54

Bonjour ThauThème, bonjour le forum,

J'ai bien déclaré les variables [Private] en début de module.

Je remet le fichier joint sans verrou (encore désolé).

19systeme-de-gestion-des-detecteurs-de-gaz-vf.xlsm (112.15 Ko)
Le 25/03/2015 à 12:17

Bonjour Seybi, bonjour le forum,

Mais... Je t'ai donné le code dans mon post précédent !...

Le 25/03/2015 à 14:30

Bonjour ThauThème, bonjour le forum,

Merci tout fonctionne parfaitement !

@+

Rechercher des sujets similaires à "afficher modifier valeurs plages filtrees"