CODEWITHSUNDEEP
vba
user1818042
user1818042

Reputation: 73

How to add/get items in Combobox in excel VBA

How do I add/get (Desc as valuember and name as displaymember same like .NET) to the combobox and listbox?

Basically, I want to show Name to the user and after selecting Name from combobox, I want to get the Desc based on Name selection for further use.

using below code I am adding Name into the combobox but don't know how add DESC as itmeindexColumn(which is DESC column from DB). (same like valuemember,Displaymember in .NET ComboBox) 

Dim rs As New ADODB.Recordset
Dim sqltextexec As String
Set rs = New ADODB.Recordset

sqltextexec = " SELECT Name , Name + '-' + Code 'Desc' from Employee "
    rs.Open sqltextexec, cn
    rs.MoveFirst
With Sheets("Sheet1").ComboBox1
    .Clear
    Do
        .ComboBox1.AddItem rs![Name]
        rs.MoveNext
    Loop Until rs.EOF
End With

Upvotes: 1

Views: 1712

Answers (1)

A.S.H
A.S.H

Reputation: 29332

First, you have a typo in the presented snippet

.ComboBox1.AddItem rs![Name] --> .AddItem rs![Name]

To refer to the selected index and the text of the combobox, use the .ListIndex and .Value respectively. You can also use .Text for the text. Example:

Private Sub ComboBox1_Change()
    Debug.Print ComboBox1.ListIndex, ComboBox1.Value, ComboBox1.Text
End Sub

Upvotes: 1

Related Questions