VBA - Adding ComboBox and the related code

April 2018

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:

Published by jak58. Latest update on March 1, 2011 at 11:40 AM by jak58.
This document, titled "VBA - Adding ComboBox and the related code ," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (https://ccm.net/).
Excel - Search for name & return column value
VB6 - Manage INI files + Miscellaneous functions