Mon Aide-Mémoire
Visiteur N°:

Valeurs uniques dans une Combo et remplir avec elles une seconde Combo

 

Depuis un tableau de données, boucler sur la première colonne (Type) pour retrouver chaque valeur de manière unique. Ensuite, remplir une première combobox avec ces valeurs. S'en servir comme critère de filtre pour remplir une seconde combobox (Marque), dont les items sont en rapport avec la sélection de la combobox1.

Enfin, lorsque la sélection est complète, remplir les cases du formulaire avec les informations attachées.Surligner d'une couleur (en rouge, par exemple) la ligne en question

Sous Excel 97, lors du démarrage de l'application, le formulaire s'initialisait correctement, l'application se déroulait sans problème.

Si je déchargeais le formulaire pour le faire réapparaître par la suite, j'obtenais un message d'erreur "Unable to set the colorindex property of the interior class" (ou son pendant en français) lorsque je pointais la combobox2 et le débogueur de code stoppait effectivement sur une ligne contenant .Interior.ColorIndex. Or, cette instruction est correcte...

D'où pouvait bien venir cette erreur...

A force d'expérimenter, je me rendis compte que c'était un problème de focus; en effet, sous Excel97 les formulaires sont modaux. En appuyant sur le bouton "Formulaire", le focus ne pointait plus sur la feuille active et le code venait bogger sur la première instruction se référant à la feuille... voilà pourquoi, il faut ajouter la ligne ActiveSheet.Range("A1").Select (ceci replace le focus sur la feuille) dans le bouton "Formulaire" avant la ligne UserForm1.Show


A la demande générale, comment ça fonctionne....

Pour la première combobox
On ne se casse pas la tête, il suffit de faire un copier-coller du code ci-dessous dans le module VBA de l' USF, même pour vos propres fichiers, la seule chose à personnaliser est le Range de la ligne Set AllCells = et si vous ne voulez traiter qu'une seule combobox, supprimez la ligne ComboBox2.Clear. Le code :

Private Sub ComboBox1_Enter()

'procédure trouvée chez http://j-walk.com/ss/excel/tips/tip47.htm
    Dim AllCells As Range, Cell As Range
    Dim NoDupes As New Collection
    Dim i As Integer, j As Integer
    Dim Swap1, Swap2, Item
    
    ComboBox1.Clear 'nettoie la combobox1
    
    Set AllCells = Worksheets("Sheet1").Range("A2", Range("A2").End(xlDown).Address)
    
'   La ligne suivante ignore l'erreur causée
'   par la tentative d'ajout d'une clé dupliquée dans la collection.
'   Le duplicat n'est pas ajouté - c'est ce que l'on désire!
    On Error Resume Next
    For Each Cell In AllCells
        NoDupes.Add Cell.Value, CStr(Cell.Value)
'       Note: le 2nd argument (key) avec la méthode Add  doit être un string
    Next Cell

'   Resume normal error handling
    On Error GoTo 0

'   Trie la collection (optionnel)
    For i = 1 To NoDupes.Count - 1
        For j = i + 1 To NoDupes.Count
            If NoDupes(i) > NoDupes(j) Then
                Swap1 = NoDupes(i)
                Swap2 = NoDupes(j)
                NoDupes.Add Swap1, before:=j
                NoDupes.Add Swap2, before:=i
                NoDupes.Remove i + 1
                NoDupes.Remove j + 1
            End If
        Next j
    Next i
    
'   Ajoute les items triés et non-dupliqués dans une ComboBox
    For Each Item In NoDupes
        ComboBox1.AddItem Item
    Next Item
ComboBox2.Clear
End Sub

Pour la seconde combobox

Ici, en fait nous devons traiter deux événements
Le premier, au moment où nous entrons - cliquons dans la combobox

Private Sub ComboBox2_Enter()
Dim vVariable As String
'on initiale la combo
ComboBox2.Clear
'on efface une ligne rouge résiduelle éventuelle sur la feuille
ActiveSheet.Range("A2:E9").Interior.ColorIndex = xlColorIndexNone
'on récupère le contenu de la première combo
vVar = ComboBox1.Value
'on remplit la seconde combo en se servant de la valeur de la première comme filtre
For Each c In Range("B2", Range("B2").End(xlDown).Address)
  If c.Offset(0, -1).Value = vVar Then
  ComboBox2.AddItem c.Value
  End If
Next
End Sub

Le second survient après un changement dans la seconde combobox, on remplit alors les différents textbox's du USF

Private Sub ComboBox2_Change()
Dim vVar1 As String, vVar2 As String
vVar1 = ComboBox1.Value: vVar2 = ComboBox2.Value
For Each c In Range("A2", Range("A2").End(xlDown).Address)
 If c = vVar1 And c.Offset(0, 1) = vVar2 Then
  c.EntireRow.Cells(1, 1).Interior.ColorIndex = 3
  c.EntireRow.Cells(1, 2).Interior.ColorIndex = 3
  c.EntireRow.Cells(1, 3).Interior.ColorIndex = 3
  c.EntireRow.Cells(1, 4).Interior.ColorIndex = 3
  c.EntireRow.Cells(1, 5).Interior.ColorIndex = 3
  TextBox1 = c.Offset(0, 2)
  TextBox2 = c.Offset(0, 3)
  TextBox3 = c.Offset(0, 4)
 End If
Next
End Sub

Finalement le code du bouton Stop

Private Sub CommandButton1_Click()
  Unload UserForm1 'décharge le formulaire
  'remet la couleur à rien, donc noire
 ActiveSheet.Range("A2:E9").Interior.ColorIndex = xlColorIndexNone
End Sub

Je vais ouvrir une petite parenthèse pour donner un mot d'explication sur le concept de l'OFFSET
Ceci est vraiment intéressant. Excel lui sait toujours quelle cellule est en cours de traitement,
que ce soit lorsque vous remplissez des données ou que ce soit lors de l'exécution d'un code VBA.
Donc, pensez comme lui et demandez vous toujours où se trouve son pointeur;
quelle cellule est concernée à un moment précis... Pour cela, petit truc quand vous programmez:
Faites un pas à pas avec la touche F8
et allez voir où se trouve le curseur dans la feuille à chaque pression de F8, placez une ligne
que vous supprimerez plus tard qui ferait par exemple MaVariable.Select
ainsi le curseur se place là où se trouve le pointeur, cela vous permettra
de débugger pas mal de choses...
Mais revenons à notre offset ! donc comme dit plus haut, Excel sait où il se trouve et bien,
grâce à offset on peut désigner une autre cellule
par rapport à la cellule en cours de traitement. C'est surtout intéressant quand il est vital
que le pointeur ne se déplace pas d'une colonne à traiter ou d'une ligne
La cellule en cours a un offset de (0,0) , si on veut désigner une cellule sur la même ligne
mais 2 colonnes plus à droite on dira var.offset(0,2);
si c'est une ligne en dessous et 5 colonnes plus à gauche ce sera var.offset(1,-5).
C'est comme si on utilisait un pointeur auxiliaire.
Voyez dans l'exemple comment se remplit les textbox's

Cliquez ici pour télécharger le fichier exemple, enregistrez le sur votre disque
Ne l'ouvrez pas depuis le navigateur

FIN