Outlook Form - how do I incorporate a drop-down menu which is linked to XL spreadsheet?

forsters used Ask the Experts™
I am designing an Outlook form and I need to include a list of users on a drop-down form.  I have a list of all users on an Excel spreadsheet but don't know how to make the drop down look at the Excel spreadsheet and bring in the information from it.  :D
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Hi, forsters.

You'll have to write some code behind the form to accomplish this.  Doing this is not a good practice.  If the spreadsheet is unavailable for any reason, then the form is going to fail.  


Many thanks. I am only a beginner at code so am not sure where and what to write.  Any pointers?  
Top Expert 2010
The code will have to be in the form.  With the form open in the form editor click the View Code button on the toolbar/ribbon.  You'll use the Open event of the Item object.  In it you'll have to open the spreadsheet and read the values and load them into your combobox.  Something like this
Sub Item_Open(Cancel)
    Dim objPage, objCtrl, intRow, excApp, excBook, excPage
    Set excApp = CreateObject("Excel.Application")
    'Change the file name and path on the next line'
    Set excBook = excApp.Workbooks.Open("C:\Somefile.xls")
    'Change the page number on the next line'
    Set excPage = excBook.Worksheets(1)
    Set objPage = Item.GetInspector.ModifiedFormPages
    'Change the page and control names on the next line'
    Set objCtrl = objPage("P.2").Controls("ComboBox1")
    'Change the column numbers as needed'
    Do Until excPage.Cells(intRow, 1) = ""
        objCtrl.AddItem excPage.Cells(intRow, 1)
        intRow = intRow + 1
    Set excPage = Nothing
    excBook.Close False
    Set excBook = Nothing
    Set excApp = Nothing
    Set objCtrl = Nothing
    Set objPage = Nothing
End Sub

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial