1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
|
'##########################################
'### Nécessite la référence ###
'### Microsoft Forms 2.0 Object Library ###
'### C:\WINDOWS\system32\FM20.DLL ###
'##########################################
'### Constantes à adapter selon votre gré ###
Const NOM_COMBOBOX As String = "maComboBox"
Const CLASSEUR_B As String = "B.xls"
Const CHEMIN As String = "C:\"
Const FEUILLE_SOURCE As String = "source"
Const DEPART_DATA As String = "A1" 'cellule de départ des données
Const COLONNE_AFFICHAGE_COMBO As Long = 1
Const CELLULE_LIEE As String = "C1"
'############################################
Sub ChargeComboBox()
Dim WB As Workbook
Dim S As Worksheet
Dim R As Range
Dim var
Dim lastLig&
Dim nbCol&
Dim i&
Dim A$
Dim OL As OLEObject
Dim CB As MSForms.ComboBox
Set S = ThisWorkbook.Sheets(FEUILLE_SOURCE)
Set R = S.Range(DEPART_DATA)
lastLig& = S.Range(S.Cells(65536, R.Column), S.Cells(65536, R.Column)).End(xlUp).Row
nbCol& = S.Range(S.Cells(R.Row, R.Column), S.Cells(R.Row, R.Column)).End(xlToRight).Column - R.Column + 1
Set R = S.Range(S.Cells(R.Row, R.Column), S.Cells(lastLig&, R.Column + nbCol& - 1))
var = R
On Error Resume Next
Set WB = Workbooks(CLASSEUR_B)
If Not WB Is Nothing Then
MsgBox "Le classeur ''" & CLASSEUR_B & "'' est déjà ouvert."
Exit Sub
End If
Set WB = Workbooks.Open(CHEMIN & CLASSEUR_B)
Set S = WB.Sheets(1)
Set OL = S.OLEObjects(NOM_COMBOBOX)
On Error GoTo 0
If OL Is Nothing Then
Set OL = S.OLEObjects.Add(ClassType:="Forms.ComboBox.1")
OL.Name = NOM_COMBOBOX
End If
Set CB = OL.Object
CB.List = var
CB.ColumnCount = nbCol&
If COLONNE_AFFICHAGE_COMBO > nbCol& Then
CB.BoundColumn = 1
Else
CB.BoundColumn = COLONNE_AFFICHAGE_COMBO
End If
For i& = 1 To nbCol&
A$ = A$ & "50;"
Next i&
CB.ColumnWidths = Mid(A$, 1, Len(A$) - 1)
OL.LinkedCell = CELLULE_LIEE
OL.Left = 200
OL.Top = 200
OL.Width = 60 * nbCol&
OL.Height = 25
End Sub |
Partager