Cavaliere
03-22-2010, 01:39 PM
Hi everyone,
After trying to execute the script posted in Kbase named "Fill combobox with ordered list"
'Place all code in the Userform
Option Explicit
Dim FArray()
Dim DataList As Range
Dim MyList As String
Private Sub UserForm_Initialize()
Dim Found As Long, i As Long
Dim cel As Range
'Set Range Name to suit
MyList = "Data"
Set DataList = Range(MyList)
ReDim FArray(DataList.Cells.Count)
i = -1
For Each cel In DataList
On Error Resume Next
Found = Application.WorksheetFunction.Match(CStr(cel), FArray, 0)
If Found > 0 Then Goto Exists
i = i + 1
FArray(i) = cel
Exists:
Found = 0
Next
ReDim Preserve FArray(i)
Call BubbleSort(FArray)
ComboBox1.ListRows = i + 1
ComboBox1.List() = FArray
End Sub
..... and so on.....
and similar scripts VBA gives me the following error "Compile error: Wrong number of arguments or invalid property assignment" and highlights Range in the following string
Set DataList = Range(MyList)
I've named range in Excel with name manager. Maybe there is some additional setup in excel should be done
I've got Excel 2007
Please help me to understand the problem.
After trying to execute the script posted in Kbase named "Fill combobox with ordered list"
'Place all code in the Userform
Option Explicit
Dim FArray()
Dim DataList As Range
Dim MyList As String
Private Sub UserForm_Initialize()
Dim Found As Long, i As Long
Dim cel As Range
'Set Range Name to suit
MyList = "Data"
Set DataList = Range(MyList)
ReDim FArray(DataList.Cells.Count)
i = -1
For Each cel In DataList
On Error Resume Next
Found = Application.WorksheetFunction.Match(CStr(cel), FArray, 0)
If Found > 0 Then Goto Exists
i = i + 1
FArray(i) = cel
Exists:
Found = 0
Next
ReDim Preserve FArray(i)
Call BubbleSort(FArray)
ComboBox1.ListRows = i + 1
ComboBox1.List() = FArray
End Sub
..... and so on.....
and similar scripts VBA gives me the following error "Compile error: Wrong number of arguments or invalid property assignment" and highlights Range in the following string
Set DataList = Range(MyList)
I've named range in Excel with name manager. Maybe there is some additional setup in excel should be done
I've got Excel 2007
Please help me to understand the problem.