Alimenter combobox hors userform

Bonjour à tous,

J'ai un problème un peu particulier concernant une combo box.

Nous désirons afficher et masquer des combobox avec des listes de rayons, mais nous voulons que cette combobox soit à un endroit précis. Ce qui ne peut être le cas d'un userform qui même si on lui indique des cordonnées ne sera jamais précisément ou l'on veut qu'il soit, dans la cellule C5 par exemple (ou alors je n'ai pas trouver la solution et elle serai plus que bienvenue).

Nous avons donc pris le parti pris de créer une combobox en contrôle active X, mais cette combobox ne doit s'afficher que si l'on clique sur un bouton qui appelle cette combobox. La seule solution que j'ai trouver est donc de recréer cette combobox dès qu'on clique sur le bouton et de la supprimer dès que l'on clique sur un autre bouton. Je n'ai donc pas la possibilité de l'initialiser la combo à l'ouverture du fichier comme il est courant de le faire.

Sub liste_pf()

    ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
        DisplayAsIcon:=False, Left:=920.294117647059, Top:=752.647058823529, _
        Width:=261.176470588235, Height:=32.6470588235294).Select

    ActiveSheet.Shapes("ComboBox1").IncrementTop -2.6470866142

    ActiveSheet.Shapes.Range(Array("ComboBox1")).Select
    ActiveSheet.Shapes("ComboBox1").ScaleHeight 0.6256692913, msoFalse, _
        msoScaleFromTopLeft
    ActiveSheet.Shapes("ComboBox1").IncrementTop 4.4118110236

End Sub

La combobox étant créée je dois maintenant l'alimenter et c'est là que je rencontre le plus gros problème, puisqu'il faut initialiser cette combobox dans un premier temps par une private sub userform_initialize.

Private Sub userform_initialize()

    For i = 117 To 152 Step 1
         With ComboBox1
            If Right(Sheets(i).Name, 2) <> " D" Then ComboBox1.AddItem Sheets(i).Name
         End With
    Next

End Sub

Je ne trouve malheureusement rien pour faire le lien entre le module ou se situe la création de la combobox et la private sub qui alimente la combobox...

La liste permettant de me diriger vers l'onglet sélectionné marche aussi niquel.

Private Sub ComboBox1_Change()
    Worksheets(ComboBox1.Value).Activate
End Su

Si quelqu'un à une solution pour la création et l'alimentation je suis plus que preneur.

Le fichier contenant des informations confidentiels je ne peux malheureusement pas le transmettre.

Bonjour Thomas,

Est-ce qu'on peut proposer une autre méthode?

Dans la démo jointe :

* une combobox activeX réglée à visible=False

* 2 boutons reliés à la même procédure "Clic"

* 2 listes

* un clic sur un des boutons affiche la combobox au dessous avec le liste A ou B (en fonction du bouton)

* après le choix dans la liste du combo, la valeur s'inscrit au dessus du bouton et la combo se masque.

Le code est simple, une partie dans un module, une autre dans le module de la feuille.

Est-ce que ça répond à la question?

Pierre

Code module ordinaire :

Sub Clic()
Dim lg As Integer, cl As Integer

    With ActiveSheet
        Col = .Shapes(Application.Caller).TopLeftCell.Column
        cl = IIf(Col = 4, 1, 2)
        lg = .Cells(Rows.Count, cl).End(xlUp).Row
        .ComboBox1.Top = 50
        .ComboBox1.Left = ActiveSheet.Columns(Col).Left
        .ComboBox1.Visible = True
        Erase .ComboBox1.List
        .ComboBox1.List = .Range(.Cells(1, cl), .Cells(lg, cl)).Value
        .ComboBox1.ListIndex = 0
    End With

End Sub

Code module feuille :

Private Sub ComboBox1_Change()

    If Me.ComboBox1.ListIndex > 0 Then
        Me.Cells(1, Col).Value = Me.ComboBox1.Value
        Me.ComboBox1.Visible = False
    End If
End Sub

C'est pas mal du tout sauf que lorsque j'ouvre le fichier et appuie sur l'un des deux boutons il m'affiche une erreur d'exécution

Ah oui, à la première utilisation la liste étant vide le Erase génère une erreur. Un simple on error resume next suffit ici pour réparer

Désolé pour ce défaut

Je ne suis pas sur de comprendre ce que fait cette ligne exactement

 Col = .Shapes(Application.Caller).TopLeftCell.Column

Alors :

* .Shapes(Application.Caller) c'est la forme qui appelle la procédure liée (donc un des boutons)

* .TopLeftCell c'est la cellule de référence du bouton contenant le coin supérieur gauche

* .TopLeftCell.Column est donc la colonne contenant le coin sup-ghe du bouton (on peut aussi récupérer la ligne si besoin => .TopLeftCell.row)

Donc ici dans la ptite démo simplette soit la colonne 4 soit la 7

Cette technique permet ainsi d'écrire une seule procédure pour tous les boutons qui doivent avoir un comportement similaire. J'ai utilisé ceci dans cette démo pour montrer qu'on pouvait changer le contenu de la liste déroulante en fonction du bouton cliqué. Mais c'est peut être pas utile dans votre projet (en fait je n'ai pas du bien comprendre votre besoin ...)

En fait ce que je veux faire n'est pas d'afficher une nouvelle combo box mais de masquer la première.

Les autres combo box dépendront d'autres boutons indépendant de ces deux premiers boutons.

Comment affecter .Shapes(Application.Caller) à ma combo box?

merci pour vos réponses qui m'aident déjà beaucoup !

Est-ce que vous pouvez mettre une maquette simplifiée de votre projet avec qq données factices pour qu'on puisse vous répondre plus efficacement? (j'avoue ne pas bien comprendre ce que vous voulez faire)

J'ai réussi a trouver comment faire. En fait je ne devais pas créer un bouton en activex mais en contrôle de formulaire.

A partir de la j'ai pu appliquer le code que vous m'avez donné pour mon cas spécifique.

Merci à vous !