MS Office Excel Ribbon Dynamic Combobox Items from SQL Server Database

1. Create Excel Workbook
2. Add CustomUI:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui"> <ribbon startFromScratch="false"> <tabs> <tab id="customTab" label="TabLabel"> <group id="dd" label="GroupLabel"> </comboBox> <comboBox id="Combo1" label="ComboBox" getItemCount="cboItemCount" getItemLabel="cboLabel" onChange="MyCombo"> </group> </tab> </tabs> </ribbon> </customUI>


3. Open VBA Editor and paste below code
4. Customize sql connection and query


Option Explicit
Public gobjRibbon As IRibbonUI
Dim cboData() As Variant

'sub running after changing combobox item
Public Sub MyCombo(cbo As IRibbonControl, strText As String)
   MsgBox "combo value selected = " & strText
End Sub

'sub adding items from cdoData collection
Sub cboLabel(control As IRibbonControl, Index As Integer, ByRef label)
    label = cboData(Index)
End Sub

'sub populating cdoData collection
Sub cboItemCount(control As IRibbonControl, ByRef count)
Dim con As ADODB.Connection
Set con = New ADODB.Connection
Dim strConn As String

'choose connection authentication type

'dataBase authentication:
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=DataBaseServer;USER ID=DataBaseUserName;Password=Password;Initial Catalog=DataBase;MultipleActiveResultSets=True"

OR

'Windows authentication:
strConn = "PROVIDER=SQLOLEDB;DATA SOURCE=DataBaseServer;Initial Catalog=DataBase;Integrated Security=true;MultipleActiveResultSets=True"


con.Open strConn
Dim i As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
rst.ActiveConnection = con
rst.Open "select distinct YourItem FROM Table order by YourItem desc "
ReDim cboData(i) ' Labels is a dynamic array
i = 1
Do Until rst.EOF
ReDim Preserve cboData(i)
    cboData(i) = rst!data_danych
    rst.Move 1
    i = i + 1
Loop
count = i

rst.Close
con.Close
Set rst = Nothing
Set con = Nothing
End Sub