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))
dt.Columns.Add(dcName)
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
dt.Rows.Add(dr)
Next
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?
Thanks
prins