[RESOLVED] Excel cell,listbox and sql server

[pandey_om]

i have
1) a sql server table
country code country name
1 india
2 usa
3 japan

2)Listbox lstCountry on a form 3) excel sheet cell()

i wana
1) display all country name in list box (I have done it by recordset,additem)
My Problem -->2)when country name selected in listbox ,corresponding
country code insert in excel sheet cell (i can use itemdata property but how)
any code or advice
PLZ help me i m coding on VBA

[pandey_om]

i coded like that


Dim strQuery As String
Dim ADOConn As New ADODB.Connection
Dim ADORset As New ADODB.Recordset

Set ADOConn = New ADODB.Connection
ADOConn.Open strConnectionString
strQuery = "SELECT DISTINCT Country_Name " & _
"FROM Country " & _
"ORDER BY country_name; "
Set ADORset = ADOConn.Execute(strQuery)
Do While Not ADORset.EOF
lstCountry.AddItem ADORset.Fields("Country_Name")
ADORset.MoveNext
Loop
ADORset.Close
Set ADORset = Nothing
ADOConn.Close
Set ADOConn = Nothing

[DKenny]

You ned to convert your listbox to a 2 column listbox, and then store both the code and description for each country in it.

To achieve this you will need to do the following

1/ Change the ColumnCount property of your LB to "2".
2/ Change the ColumnWidths property of your LB to "0pt;1pt". (You want to hide the 1st column as that is where we will be storing the country code.)
3/ Set the BoundColumn property of your LB to "1". (You want the vlaue of the LB to be the code which is in column 1, rather than the name which is in column 2.)
4/ Change the SELECT staement in your strQuery variable to

SELECT DISTINCT Country_Code,Country_Name

You can leave the rest of the SQL as is.
5/ Change your loop to the following, which will add both the code and name of each country to the LB.

Do While Not ADORset.EOF
    
        'The next line adds a new row to the LB
        'and puts the counrty code in the first
        'column of that row
        Me.ListBox1.AddItem ADORset.Fields("Country_Code")
        
        'The next line adds the country name to the 2nd column
        'of the row we cretaed in the line above
        Me.ListBox1.Column(1, Me.ListBox1.ListCount - 1) = ADORset.Fields("Country_Name")
        
        ADORset.MoveNext
    Loop

Now whenever you return the value of the Listbox - it will contain the country_code of the chosen Country Name.

[pandey_om]

thank u i will try it

[pandey_om]

what about my problem no. 2(insert into cell-"Countrycode" )

[DKenny]

You can do this with the _Change event for the listbox, here's an example.

Change the refrence to A1 in the following to refer to the cell name where you want the value stored.
Also change the procedure name to be yourListboxName_Change
( I hope that makes sense)

Private Sub ListBox1_Change()
    Range("A1").Value = Me.ListBox1.Value
End Sub

[pandey_om]

Thanks Declan,
my problem mostly solved.