Free Support Forum -

How to set the options in a list control?

When I tried AutoFilter like

myExcel.Worksheets(0).AutoFilter.Range = "C1:C1"

I got a dropdown list cell C1. But how to set the options in the list?. Also I need the options dynamic. How can I use a range object as the source of a list control?

Following is the code I used to create the Range

Dim sheet As Worksheet

sheet = myExcel.Worksheets(1)

sheet.Cells.ImportDataTable(getDateTable, False, 0, 0)

' where getDateTable is a function which returns a Datatable [see below]

sheet.Name = "Dates"

Dim myRange As Range = myExcel.Worksheets(1).Cells.CreateRange("A1", "A7")

myRange.Name = "Days"

'------------ ------------ --------------- -------------

Private Function getDateTable() As DataTable

Dim dt As New DataTable

Dim dcName As New DataColumn("Day", GetType(String))


Dim dr As DataRow

Dim arrDays() As String

arrDays = New String() {"Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun"}

For Each strDay As String In arrDays

dr = dt.NewRow()

dr("Day") = strDay



Return dt

End Function

'------ ------------ ---------- -----------

I want the items in the range I created above as the options in a listcontrol in my worksheet. Is it possible?



Please check for setting autofilter option.

To create a list based on a range, you just need to create the autofilter on the first cell.

Dim startCell as String = Cells.CellIndexToName(range.FirstRow, range.FirstColumn)

sheet.AutoFilter.Range = startCell + ":" + startCell