I have a datatable pulled from a SQL query. I would like to add a column on it that only allows the option of being “Yes” or “No”. For that added column, I have changed the type to “documentformat.openxml.office.customui.dropdown” which I would assume is the type I need but I’m not sure. In addition, I’m not sure what to set the default value to so that it gives just the 2 options. Has anybody else done something like this? In writing a new Excel file, can I make a that allows for this? Thanks in advance.
Like this you want…? its possible if you use macros i guess.
for you Reference
You can also create this by invoking code if that would be more efficient. Here is some sample code and test file that you can easily adapt to your needs.
Dim excel As Microsoft.Office.Interop.Excel.Application Dim wb As Microsoft.Office.Interop.Excel.Workbook Dim ws As Microsoft.Office.Interop.Excel.Worksheet Dim rng As Microsoft.Office.Interop.Excel.Range excel = New Microsoft.Office.Interop.Excel.ApplicationClass wb = excel.Workbooks.Open(Filename) excel.Visible=False ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet) rng=CType(ws.Range(PlaceRange),Microsoft.Office.Interop.Excel.Range) With rng .Validation.Add(Microsoft.Office.Interop.Excel.XlDVType.xlValidateList, Microsoft.Office.Interop.Excel.XlDVAlertStyle.xlValidAlertStop, Microsoft.Office.Interop.Excel.XlFormatConditionOperator.xlBetween, ValidationFormula) .Validation.IgnoreBlank = True .Validation.InCellDropdown = True End With wb.Save wb.Close
Any questions just ask,
AddDropdownVB.zip (10.8 KB)
Thank you for all the responses. I will check some of this out this morning and see if I’m successful with it.
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.