none
Run a macro to load data into a combo box RRS feed

  • Question

  • Hello,

    these are the steps i've made until now in order to load data into a combobox (more than 25, then impossible to use a dropdown form) that should appear in a text form field. Well, but it doesn't work.

    can you please tell me how to do it??

    1 step - create a list in an excell file. Sheet1("A1:A30") excel file name : countries

    2 step - create a text form field in word doc (in properties: "Run macro "Countries" on entry")

    3 step - create a userform1 - combobox - caption:countries

    4 step - create code to userform1

    Private Sub userform_initialize()

    Combobox1.columncount = 1
    Combobox1.Clear
    Combobox1.AddItem "Countries"
    Dim countriesxl As Object

    With ActiveDocument.InlineShapes(1).OLEFormat.DoVerb
    Set countriesxl = ActiveDocument.InlineShapes(1).OLEFormat.Object

    A1 = 0
    Do
        A1 = A1 + 1
        dest = coutriesxl.worksheets("sheets1").Cells(A1, 1)
        Combobox1.AddItem dest
    Loop Until det = ""

    Combobox1.ListIndex = 0
    End With
    End Sub

    5 step - create code module 1

    sub countries()

    userform1.show

    End sub

    6 step  - create code "this document"

    Private sub document_open()

    load userform1

    End Sub

    Many thanks in advance

    Friday, August 12, 2011 4:36 PM

Answers

  • Hi JMSBAR,

    Sorry for the late response.

    If you want to work with OLEObject in Word program, you need to active the object first. Please check the VBA code below:

    Private Sub UserForm_Initialize()
    Dim doc As Document
    Set doc = ThisDocument
    
    Dim inShape As InlineShape
    Set inShape = doc.InlineShapes(1)
    
    With inShape.OLEFormat
    .Activate
    For i = 1 To 30
    ComboBox1.AddItem pvargItem:= _
    .Object.Worksheets(1).Cells(i, 1).Value
    
    Next i
    
    .Object.Application.Quit
    End With
    
    End Sub
    


    The code works fine on my side, you can take a try.

    Have a nice day.


    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, August 23, 2011 11:44 AM
    Moderator