topic Re: ods tagsets.excelxp and creating combo boxes in ODS and Base Reporting

ods tagsets.excelxp and creating combo boxes

Lorelle — Tue, 21 May 2013 20:00:35 GMT

I have 5 columns at the end of my excel file that I would like users to fill in. Patient enrolled = Yes or No. So I want the user to see a combo box for each record and then they can select Yes or No. Is there a way to create a combo box using ods tagsets.excelxp?

Re: ods tagsets.excelxp and creating combo boxes

Cynthia_sas — Tue, 21 May 2013 21:47:08 GMT

Hi:

I'm going to guess that the answer to that question is NO. SAS is not creating a "true, binary" Excel file when you use TAGSETS.EXCELXP. SAS is creating a Spreadsheet XML/Office 2003 XML file that Excel knows how to treat "like" an Excel workbook. But many capabilities of Excel (for example, setting a password or pull down menu choices) are not controllable from the SAS end of things. I just checked in Excel and the choice for a making a form with a combo box is under Developer-->Insert as shown in the screenshot. I do not believe this is possible with TAGSETS.EXCELXP. You may want to check with Tech Support.

You might consider writing a VB script to post process the file and make the combo boxes for you, then save the file as XLS or XLSX.

cynthia

Re: ods tagsets.excelxp and creating combo boxes

Reeza — Tue, 21 May 2013 21:51:12 GMT

Personally, I wouldn't use combo boxes, but data validation to restrict it to a drop down list.

This way you can apply it to a large range of cells at once and control the error messages.

Apply data validation to cells - Excel - Office.com

See the section on

Restrict data to predefined items in a list

Same answer as Cynthia though, I don't think you can do this via tagsets.

Re: ods tagsets.excelxp and creating combo boxes

Lorelle — Wed, 22 May 2013 11:54:15 GMT

Thank you for your response Reeza. Regarding using/not using combo boxes - that all depends on what the programmer and/or end-users are trying to accomplish with the excel file.

Re: ods tagsets.excelxp and creating combo boxes

Lorelle — Wed, 22 May 2013 11:54:51 GMT

Thanks Cynthia!

Re: ods tagsets.excelxp and creating combo boxes

boschy — Fri, 31 May 2013 05:41:11 GMT

What you can try is adding combo boxes to a spreadsheet in Excel exactly how you want it. Then save the spreadsheet as XML.

Open the XML text file in an editor, and locate the combo box XML definitions. There might be a way to embed them from the SAS side into your ExcelXP output file in those columns. Sounds messy, though...

Re: ods tagsets.excelxp and creating combo boxes

Lorelle — Fri, 31 May 2013 15:11:15 GMT

Thank you for your response boschy.