Creating Excel with dropdown option

Help
1

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.

2

Like this you want…? its possible if you use macros i guess.

3

for you Reference

4

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)

1 Like
5

Thank you for all the responses. I will check some of this out this morning and see if I’m successful with it.

6

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.