I'll see if I cannot come up with an example of this and get a file uploaded
to my site that you can download and examine as an example. That may take me
a day or two, full schedule until this weekend at least.
I'm posting through the discussion forum at Microsoft, so am not familiar
with how to upload a file at OfficeKB.com - someone else will have to help
with that answer.
"tkraju via OfficeKB.com" wrote:
Thank you once again ,JLatham. Past three days I tried to do this task as
you explined in #2 way. I could not succeed. Lastly I give up to do this
complex task.Being a new (curious) learner (Excel and VBA) I used to build
these small projects to test my self how much I learned, but not for any
professional use.Actully I have done this project in excel w/sheet.Its
working fine.Then I thought why not in a userform/or dialogbox. In excel
w/sheet my database is in same sheet and my function result list is also in
same sheet visible for user.My point is a user should not see my database,and
my function result list .I thought why not my function results go to directly
a combobox list or listfill range.I would like to mention here that in many
websites I used to see a 'search' box to search anything,which retrives any
thing that matches the user input text in that 'search' box. With this
interesting key feature I had an idea to generate same thing in excel,by
which I wrote this 'formula ' which I have quoted in my question.This small
project in my excel w/sheet working fine.Any way I will learn one day and
tranfer this w/sheet project into vba project.By the way how to attach a
w/book.xls to the post in this forum?.Thank you so much....
JLatham wrote:
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").FormulaA rray = myFormula
or to a range of cells using code similar to this:
Worksheets("NameOfSheet").Range("A1202:A1215").Fo rmulaArray = 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.
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
[quoted text clipped - 30 lines]
how to call a function on control click event and put the function result on
another control. in userform.
--
Message posted via http://www.officekb.com