You can't do this in the way you are thinking at the moment. You cannot call
a formula that is on a worksheet from a user form. Two ways to do what you
want to do that I can think of a few ways to do this:

#1 - you put your array formula on the worksheet as is normally done, but
where you have ,search textbox.text, within that formula use a cell reference
on the worksheet itself. You could then have the code on the Search button
put what was typed into the search textbox into that cell on the worksheet.
Assume you want to put it into cell X1201. Your code would look something
like this:

Worksheets("NameOfSheet").Range("X1201").Value = Me![Search Textbox].Text

By the way, rename your controls so that their names don't contain spaces or
special characters like hyphens (-), it really confuses things when working
in VB. I suggest names like SearchTextbox and SearchResults or
Search_Textbox and Search_Results.

#2 - you could build the formula in the code and assign it to cells on the
worksheet, something like this. The space with an underscore indicates that
the instruction continues in the next line in VB:

Dim myFormula As String

myFormula = "=IF(ROWS($1:1)<=COUNTIF(A$2:A$1200," & _
Chr(34) & Me!SearchTextbox.Text & Chr(34) & ",INDEX(A$2:A$1200" & _
",SMALL(IF(LEFT(A$:2:A$1200,3)=" & Chr(34) & MeSearchTextBox.Text _
& Chr(34) & ",ROW(A$2:A$1200)-ROW(A$2)+1,ROWS($1:1)))," _
Chr(34) & " " & Chr(34) & ")"

The Chr(34) function inserts a double-quote into the string. Assuming that
I've copied your formula correctly, and assuming that it does work, then you
can assign that formula as an array formula to one or more cells using code
like this:

Worksheets("NameOfSheet").Range("A1202").FormulaAr ray = myFormula
or to a range of cells using code similar to this:
Worksheets("NameOfSheet").Range("A1202:A1215").For mulaArray = myFormula

Of course, the NameOfSheet would be replaced by the actual worksheet name
and the range would change to be what you need it to be.

Setting the List box's list to the results is actually very easy. Assume
that you put the Array formula into A1202:A1215 and that B1202 is also
available for use, this code would put the results from the array formula
into the list box:
Me!SearchResults.ColumnCount = 1
Me!SearchResults.RowSource = "A1202:A1215"
Me!SearchResults.ControlSource = "B1202"
Me!SearchResults.BoundColumn=0

That sets up the list box to display the results of the array formula, and
for the choice that you make from that list to be echoed in cell B1202 so you
could examine that to see what a person chose and act accordingly if required.

#3 - the third way would be to 'translate' your array formula into VB code
and execute it and set things up based on the results. But that's too
complex an operation for me to solve here - and since at this point I've NOT
examined your formula for understanding as to what it's doing, I couldn't
even begin to do that yet.

I think #2 is probably what is most likely going to be the solution for you
to use. By using the .FormulaArray property, we have done the same thing in
code that you do from the keyboard when entering an array formula.

I hope that this helps you move on with your project.

"tkraju via OfficeKB.com" wrote:

Thanks JLatham,I know how to build a userform,but I am a beginner in adding a
code to a userform.I have designed a userform with 2 textboxes('search
textbox','result textbox'),1 command button('search'),1 listbox('search
results').When a user enters 3 letters in 'search textbox' and click on cmd
button 'search' ,cmdbutton click event will call an array formula {IF(ROWS($1:
1)<=COUNTIF(A$2:A$1200,'search textbox'.text),INDEX(A$2:A$1200,SMALL(IF(LEFT
(A$2:A$1200,3)='search textbox'.text,ROW(A$2:A$1200)-ROW(A$2)+1, ROWS($1:1))),
" ") , and the results of this formula be put in 'search results' listbox.
On listbox.list click event 'result textbox' will display one result from
'search result'.text. This is a tough task for me.I do understand from ur
answer how to call a function.How to do my tough task. Many thanks in
anticipation.

JLatham wrote:
Assumes a UserForm with 2 controls on it - a command button and a label.
Clicking the command button will call a function to multiply a value by 10
and put the results into the label:

Private Sub CommandButton1_Click()
Me!Label1.Caption = MultiplyBy10(40)
End Sub

Function MultiplyBy10(Quantity As Integer) As Long
MultiplyBy10 = Quantity * 10
End Function

Some controls have .Caption property, some others use .Text, depends on the
control.

I presume you know how to start building a userform and how to put controls
on it? Once you've got the controls in place, right click on the command
button and choose View Code to start adding code to its _click event.

how to call a function on control click event and put the function result on
another control. in userform.

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200608/1