05-21-2013 04:00 PM
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?
05-21-2013 05:47 PM
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.
05-21-2013 05:51 PM
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.
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.
05-22-2013 07:54 AM
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.
05-31-2013 01:41 AM
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...