I'm reverse-engineering a manually created spreadsheet for dynamic creation. Most of the cells are populated with simple data, but there are a couple that are "sort/Filter" dropdowns like so:
How can I dynamically create such a control?
Is there a way to "view source" in the Excel spreadsheet to see what sort of code might be required to produce these controls?
UPDATE
Adapting MacroMark's code, this compiles:
var rangeMonthYears = _xlSheet.Range[_xlSheet.Cells[7, 3], _xlSheet.Cells[7, 15]];
object sortFilterCombobox = (object)rangeMonthYears.AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
_xlSheet.Cells["6", "C"] = sortFilterCombobox; //MonthLabel;
...but it crashes, presenting me with this upbraiding note:
How have I gone astray?
UPDATE 2
To answer MacroMarc in his comment below, here is a screen shot of the filter control in the legacy/model spreadsheet (which I'm reverse-engineering):
In this case, I deselected "November" from the list, so that it was removed, as you can see. So what the user selects affects the visibility of the columns below.
UPDATE 3
With all this fancy-pantsiness baked into the legacy spreadsheet, I'm now considering saving it as a template and simply replacing the cell contents as needed. Is there any reason why this would not be viable?
If not, to accomplish this, should I save the existing spreadhseet "As Excel Macro-Enabled Workbook"?
UPDATE 4
I tried adapting MacroMarc's answer like so:
Range monthYearCells = _xlSheet.Range[_xlSheet.Cells[COLUMN_HEADING_ROW, MONTH1_COL], _xlSheet.Cells[COLUMN_HEADING_ROW, MONTH13_COL]];
object monthFilter = (object)monthYearCells.AutoFilter(1, System.Reflection.Missing.Value, XlAutoFilterOperator.xlAnd, System.Reflection.Missing.Value, true);
var monthFilterCell = (Range)_xlSheet.Cells[6, 3];
monthFilterCell.Value = monthFilter;
...but got the runtime exception:
Is it the last line (assigning monthFilter to the range's Value property) that's causing the problem? If so, what should I assign monthFilter to, or what should I do with it?
See Question&Answers more detail:
os