Word Macro Based On Worksheet Result

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi,


    I am trying to write a macro for a word document that when the word document opens it will perform one of four actions based on what is selected in an Excel userform combobox.


    I can't figure out how to reference the Excel workbooks userform combobox.


    For when the Word document opens I tried to write the following code to reference the Excel userform's combobox but it doesn't work:


    Code
    If Workbooks("Book 1.xls").Userform1.Combobox1.Text = "Blue" Then 
Call Turnallbackroundsblue
end if


    How do I reference an excel userform using vba for word?


    If there is no way this can be done can someone let me know so I can try to set my project up a different way.


    Thank you in advance.

    Re: Word Macro Based On Worksheet Result


    It would be easier if the excel userform wrote it's content to a cell.


    I haven't tested but I doubt you will be able to read the content of a userform in excel that you did not initiate via code from word.

    [h4]Cheers
    Andy    [/h4]

    Re: Word Macro Based On Worksheet Result


    I can write the userform results to a cell in my workbook but I still can't figure out how to reference the workbook using Word VBA.


    How do you reference the workbook cell from Word?


    Thanks again.


    (P.S. Andy you are awesome! The progress meter stuff you did is really cool!)

    Re: Word Macro Based On Worksheet Result


    This will output information about workbooks and worksheets in currently open excel.


    Add this to a word code module.
    [vba]Sub XX()


    Dim appXL As Object
    Dim lngWBKIndex As Long
    Dim lngSHTIndex As Long


    ' assumes excel is running
    Set appXL = GetObject(, "Excel.Application")
    For lngWBKIndex = 1 To appXL.workbooks.Count
    Debug.Print appXL.workbooks(lngWBKIndex).Name
    For lngSHTIndex = 1 To appXL.workbooks(lngWBKIndex).sheets.Count
    Debug.Print appXL.workbooks(lngWBKIndex).sheets(lngSHTIndex).Name
    Next
    Next

    End Sub[/vba]


    (P.S. Thanks!)

    [h4]Cheers
    Andy    [/h4]

Participate now!

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

Register Yourself Login