Mise à jour d'un ComboBox ActiveX instantané qui se trouve sur deux onlgets différents

[Matt87]

Bonjour à tous,

Je cherche une solution à mon problème sur le net, mais je suis incapable d'adapter les solutions proposées à mon projet.

J'aimerais qu'un ComboBox ActiveX se trouvant sur la feuille «Coordos» puisse se mettre à jour instantanément lorsqu'une valeur est ajoutée ou supprimée de son listFillRange qui se trouve sur un onglet nommé «Liste» de A2 jusqu'en fin de tableau list.Objects.

J'ai trouvé plusieurs solutions, mais s'adaptant seulement à un ComboBox se trouvant sur le même onglet que son listFillRange.

J'ai actuellement une solution dite temporaire mais elle celle-ci se trouve dans le module de la feuille «Coordos» dans activate ce qui implique que l'utilisateur doit changer d'onglet et revenir sur l'onglet «Coordos» pour que la liste se mettre à jour. Voici ce que j'ai présentement:

Private Sub Worksheet_Activate()

Application.ScreenUpdating = False
Dim j As Long

Set Ws = Worksheets("Liste")

ThisWorkbook.ActiveSheet.ComboBox1.Clear

With ThisWorkbook.ActiveSheet.ComboBox1
For j = 2 To Ws.Range("A" & Rows.Count).End(xlUp).Row

If Ws.Range("A" & j).Value <> "" Then
.AddItem Ws.Range("A" & j)
End If

Next j

End With

Sheets("Coordos").Activate
ComboBox1.Value = ""
ComboBox1.Activate
Application.ScreenUpdating = True

End Sub

Auriez-vous une solution à me proposer ?

Merci à l'avance pour votre temps et je souhaite à tout le monde une agréable journée !

 

[job75]

Bonsoir Matt87,

Auriez-vous une solution à me proposer

Oui et elle est évidente :

- en colonne A de la feuille "Liste" il ne doit pas y avoir de cellules vides et A1 doit contenir un titre

- définir le nom Liste dynamiquement par la formule =DECALER(Liste!$A$2;;;NBVAL(Liste!$A:$A)-1)
ou sur version anglaise =OFFSET(Liste!$A$2,,,COUNTA(Liste!$A:$A)-1)

- mettre le mot Liste dans la propriété ListFillRange de la ComboBox.

A+

 

[Matt87]

Bonjour job75,

Un gros merci pour le code. Celui-ci fonctionne partiellement.

J'ai un bouton qui supprime la ligne de l'item sélectionné dans cette même ComboBox. L'item se retire instantanément de la liste comme souhaité, mais le ComboBox m'affiche une valeur vide à la place. Pourtant, ma liste déroulante ne comporte aucune valeur vide.

 

[job75]

Bonjour Matt87, le forum,

Oui il faut forcer le recalcul du ListFillRange, placez ce code dans la feuille "Liste" :

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Coordos").ComboBox1.ListFillRange = "Liste"
End Sub

"Coordos" étant le nom de la feuille de ComboBox1.

A+

 

[job75]

Une autre solution, supprimez le nom défini "Liste" et placez dans le code de la feuille "Liste" :

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tablo, i&, x$, a(), n&
With Sheets("Liste")
    If .FilterMode Then .ShowAllData 'si la feuille est filtrée
    tablo = .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row) 'matrice, plus rapide, au moins 2 éléments
End With
For i = 2 To UBound(tablo)
    x = CStr(tablo(i, 1))
    If x <> "" Then
        ReDim Preserve a(n) 'base 0
        a(n) = x
        n = n + 1
    End If
Next
With Sheets("Coordos").ComboBox1
    .ListFillRange = "" 'sécurité
    If n Then .List = a Else .Clear
End With
End Sub

 

[job75]

Notez qu'avec la solution précédente il n'y a pas de problème avec les cellules vides.

Elles sont ignorées.

 

[Matt87]

Bonjour job75,

Ça fonctionne impec avec la deuxième solution.

Remarquez que la première solution fonctionnait également, mais si je devais supprimer la valeur en «A2», ma zone définie comme étant «Liste» se retrouvait avec une référence invalide.

Un gros merci pour le cop de main !
Je vous souhaite une agréable journée !

 

[job75]

Remarquez que la première solution fonctionnait également, mais si je devais supprimer la valeur en «A2», ma zone définie comme étant «Liste» se retrouvait avec une référence invalide.

Il suffisait de définir le nom "Liste" par =DECALER(Liste!$A$1;1;;NBVAL(Liste!$A:$A)-1)
et bien sûr de ne pas supprimer A1...