Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2009
    Posts
    79

    Unanswered: Populate a comboxbox and Listbox in a excel userform with an sql table data

    Hi All,

    I have a excel userform which has some combo boxes and list boxes and I want to populate this boxes with the data which I have on my sql server database tables. Just to give an example. My first combo box is for region which needs to be populated from a sql table called Region_Mapping and my first list box should be linked to the same sql table but it should populate the countries on the basis of the region which user will select in the combo box. For eg : In my sql table I have following countries which are mapped against America
    1) Argentina
    2) Brazil
    3) Mexico
    4) Canada.
    Now if the user selects the region America in Combo box one then the List box one should get populated with the above mentioned countries with check boxes so that user can remove the unwanted countries while extracting data. Please expedite.

  2. #2
    Join Date
    Sep 2009
    Posts
    79

    Populate a comboxbox and Listbox in a excel userform with an sql table data

    Hi,

    I have the below code so far. Following are things which I am looking for :

    1) While establishing connection to sql server database I want to enter User ID and password in my code.
    2) My Combobox gets populate with the data but it shows all instances of each region. For e.g. America is reflecting 4 times in my combo dropdown same in case of other regions. It should take only one instance of each region.
    3) As mentioned earlier I want to add a list box to my userform which will get populated on the basis of the value which i had selected in my combo box for example If I select America in my combo box my list box should get populated with the countries which are mapped against America in my sql server table and they should get populated with check boxes in listbox.

    HTML Code:
    Private Sub UserForm_Initialize()
    
     
    
        Dim cnt As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim stDB As String, stConn As String, stSQL As String
        Dim xlCalc As XlCalculation
        Dim vaData As Variant
        Dim k As Long
         
        Set cnt = New ADODB.Connection
         
        stConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=sap_data;Data Source=DB-77716EFB0313\SQLEXPRESS"
         
        cnt.ConnectionString = stConn
         
         'your  SQL statement
        stSQL = "SELECT Region FROM Region_Mapping"
        
       
         
        With cnt
            .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
            .Open stConn 'Open connection.
             'Instantiate the Recordsetobject and execute the SQL-state.
            Set rst = .Execute(stSQL)
        End With
         
        With rst
            Set .ActiveConnection = Nothing 'Disconnect the recordset.
            k = .Fields.Count
             'Populate the  array with the whole recordset.
            vaData = .GetRows
        End With
         
         'Close the connection.
        cnt.Close
         
         'Manipulate the Combobox's  properties and show the form.
        With frmdata
            With .ComboBox1
                .Clear
                .BoundColumn = k
                .List = Application.Transpose(vaData)
                .ListIndex = -1
            End With
            
        End With
         
         'Release objects from memory.
        Set rst = Nothing
        Set cnt = Nothing
    
    
    End Sub
    Please expedite.

  3. #3
    Join Date
    Sep 2009
    Posts
    79

    Populate a comboxbox and Listbox in a excel userform with an sql table data

    Hi,

    Please expedite....
    Last edited by abhay_547; 04-14-10 at 01:16.

  4. #4
    Join Date
    Sep 2009
    Posts
    79

    Populate a comboxbox and Listbox in a excel userform with an sql table data

    Hi All,

    Please expedite...

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    994
    Provided Answers: 2
    Don't know if this helps, but in my experience, controls on userforms in Excel applications will only accept data from lists on worksheets.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Sep 2009
    Posts
    79

    Populate a comboxbox and Listbox in a excel userform with an sql table data

    Hi Weejas,

    I got the below code from vbaexpress site and now the data is getting populated in combobox from sql table.

    HTML Code:
    SELECT DISTINCT CONVERT(VARCHAR(MAX), Region) FROM Region_Mapping

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •