Linking Data In A Vba Combo Box

  • I have created a form that pops up when I open a new template. In that form I have inserted a combo box that I want to be able to select data from a separate spreadsheet that is on our server. What code do I need to write to link this data to my combo box. Below is what I have written.



    Can anyone help me?

  • Re: Linking Data In A Vba Combo Box


    Try this

  • Re: Linking Data In A Vba Combo Box


    Here's a solution that does not require the workbook to be open.


    Step1.
    In the VBE, set a reference to MDAC 2.5 or above (ADO Library)
    (Microsoft ActiveX Data Objects Library (2.5 version or later)


    Step2
    Paste the following code into your userform code


    [vba]
    Private Sub UserForm_Initialize()
    'the code requires a reference to be set in the VBE to the following
    'Microsoft ActiveX Data Objects 2.5 Library or later version
    Dim cnt As ADODB.Connection
    Dim cmd As ADODB.Command, rst As ADODB.Recordset
    Dim stCon As String, stSQL As String, k As Long
    Dim vaData As Variant

    Set cnt = New ADODB.Connection
    Set cmd = New ADODB.Command


    'here we set up the connection string for your source data spreadsheet
    'change the path to suit your source file location


    stCon = "Provider=Microsoft.Jet.OLEDB.4.0;" _
    & "Data Source=C:\Billing Schedule.xls;" _
    & "Extended Properties='Excel 8.0;HDR=No'"

    'the SQL string to retrieve the combobox data
    stSQL = "SELECT * From [MASTER SCHEDULE$A5:A103]"

    'connect to the datasource
    cnt.ConnectionString = stCon

    With cnt
    .CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
    .Open stCon 'Open connection.
    'load the Recordsetobject and execute the SQL statement
    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

    'Manipulate the Combobox's properties and load the retrieved data
    With Me
    With .ComboBox1
    .Clear
    .BoundColumn = k
    .List = Application.Transpose(vaData)
    .ListIndex = -1
    End With
    End With


    'Cleaning up.
    Set cmd = Nothing
    cnt.Close
    Set cnt = Nothing


    End Sub
    [/vba]


    Step3
    Amend the connection string to match your file location.


    I have tested this and it works fine on my test data. Hope it helps.

  • Re: Linking Data In A Vba Combo Box


    Thanks for that response. I have implemented that code in my project. Now it comes up with an error Type mismatch when I try to show the form. Is this because the form should be named the same as the Private Sub UserForm_Initialize(), i.e. Private Sub fmCustomerData_Initialize()?


    Below is the main part of my code. Any help appreciated.


  • Re: Linking Data In A Vba Combo Box


    Have you set a reference to MDAC 2.5 or above (ADO Library)
    (Microsoft ActiveX Data Objects Library (2.5 version or later) as required and mentioned in my post ?

  • Re: Linking Data In A Vba Combo Box


    what happens if you run a procdure that just loads the userform (as opposed to running it in the procedure you list above. The above code may well interfere with the initialise event


    Like I said, it works in the test file I set up.

  • Re: Linking Data In A Vba Combo Box


    I managed to get the userform to load by changing the sub name to Private Sub fmCustomerData_Initialize(), however I can't get it to load the data into the combo box. I've also tried it on a different computer but can't get it to work on either. I've tried setting the ADO library to v2.5 and v2.8 but this has no effect. Any other suggestions?

  • Re: Linking Data In A Vba Combo Box


    Quote from Will Riley

    what happens if you run a procdure that just loads the userform (as opposed to running it in the procedure you list above. The above code may well interfere with the initialise event


    Like I said, it works in the test file I set up.


    Did you try what i suggested ?

  • Re: Linking Data In A Vba Combo Box


    I tried creating a new module with the following code to test it out but had no success. Any further suggestions? I'm running Excel 2003 SP2.



    Code
    Sub Test()
    fmCustomerData.Show
    End Sub
  • Re: Linking Data In A Vba Combo Box


    Will's code populates a ComboBox on a userForm. Your code seems to create a new workbook than show a Form, tries to write data from the form to the sheet. I can't see where the form is loaded using Will's code nor if it is a new worbook where the form come's from.


    Are you actually using a UserForm?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!