Creating a custom form using external data for drop-down lists

Question

Hi!  I need to create a shareable form to allow users to enter time spent on various clients.  I want the contents of some of the drop-down lists to be populated using an external database (i.e. Excel).

I tried creating this form in Excel using macros, VBA and ActiveX controls, but I'm not able to share it (multiple people can't access it at the same time).

I've been told that I can create this in Sharepoint but not sure that will work?  I opened up Microsoft Forms Pro and it doesn't seem to have the functionality I need to be able to pull data from an external source to populate the drop-downs.  Is there a Microsoft product (or products) that would suit my needs?

Showing a picture of the drop-down boxes needed.

Answer 1

Hi Lisa,

As you mentioned that multiple user can’t access workbook at the same time, may I know how you are sharing the Excel workbook?

Yes, you are about Microsoft Forms pro, it does not have a functionality of getting data from external sources yet.

If you are SharePoint online user, they you can store your workbook in SharePoint or OneDrive for business library and then share the workbook with other users to co-author.

Please note, to co-author a workbook, user must have:

1. Users need an Office 365 subscription.
2. One must have the latest version of Excel for Office 365 installed.
3. Must sign in to Office with your subscription account.
4. You need to use Excel Workbooks in .xlsx, .xlsm, or .xlsb file format. If your file isn’t in this format, open the file and then click File > Save As > Browse > Save as type. Change the format to Excel Workbook (*.xlsx). Please note that co-authoring does not support the Strict Open XML Spreadsheet format.

In addition, I would like to mention that there are few things which are not fully supported yet, such as if a user adds an ActiveX Controls, then other users collaborating the xlsm workbook at the same time might see the following message. To see new changes, you must click Reopen.

Thanks,

Neha

Answer 2

Hi Lisa,

Did you check above reply? Please update us when you have time.

Regards,

Neha

Answer 3

Hi Neha,

I have shared the .xlsm workbook on Sharepoint.  All users are on Office 365 with Excel version 1908.   The form that I created doesn't seem to work properly when multiple people are in the workbook.  The users get an error message.  If we upgrade to the latest version of Excel, will this fix this issue?

I still have not found a solution.  I am working on creating a different form within Sharepoint but I'm not sure if it is able to have the functionality that I require.

Lisa

Answer 4

Hi Neha,

Thank you for your reply.

I am sharing the document in Sharepoint online.

1. Users need an Office 365 subscription.  --->  Yes
2. One must have the latest version of Excel for Office 365 installed.  ---> Not everyone has the latest version of Excel.  I am unsure if I ask them to upgrade it may impact other tools/files?
3. Must sign in to Office with your subscription account.  -->  Yes
4. You need to use Excel Workbooks in .xlsx, .xlsm, or .xlsb file format. -->  it is a .xlsm file

I am thinking that because the file has a form with drop-down boxes that users choose, it cannot operate properly in a shared environment. If multiple users are entering data in the form at the same time, Excel is asking them to create another version of the file.

I'm not sure if anything can be done or if this is a limitation to Excel currently.

Lisa