VBA - Adding ComboBox and the related code

October 2016

This code relatively simple and requires no further explanation.
Paste these two sub in a general module (eg Module1).

Option Explicit 

Sub CréerCombo(Lier As Range, Optional Sh As Worksheet, Optional Emplacement As Range) 
Dim Obj As Object 
Dim code As String 
Dim Ix As Integer, Num As String 
    If Sh Is Nothing Then 
        Set Sh = ActiveSheet 
    End If 
    Ix = ActiveSheet.OLEObjects.Count + 1 
    Num = "0" & Ix: Num = Right(Num, 2) 'Pour jusque 99 boutons 
    If Emplacement Is Nothing Then 
        'emplacement par défaut 
        Set Emplacement = ActiveCell 
    End If 
'crée le bouton 
    With Emplacement 
    Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combobox.1", _ 
    Link:=False, DisplayAsIcon:=False, Left:=.Left, Top:=.Top, Width:=.ColumnWidth * 5.65, Height:=.RowHeight) 
    Obj.Name = "ComboB_" & Num 
    End With 

'propriétés du combo 
    With ActiveSheet.OLEObjects(Ix) 
        .Placement = xlMoveAndSize 
        .PrintObject = True 
        .ListFillRange = Lier.Address 
    End With 

'Le texte de la macro 
    code = vbCrLf & "Private Sub ComboB_" & Num & "_Change()" & vbCrLf 
    code = code & "    MsgBox ""Vous avez sélectionner la ligne "" & ComboB_" & Num & ".Text, ,""ComboB_" & Num & """" & vbCrLf 
    code = code & "End Sub" 

'Ajoute la macro en fin de module feuille 
    With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule 
        .insertlines .CountOfLines + 1, code 
    End With 
End Sub

Download an example for Excel 2007:
Download project:

Related :

This document entitled « VBA - Adding ComboBox and the related code  » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.