call a function on control click event

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

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.
"tkraju via OfficeKB.com" wrote:

> 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
>
>

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

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").FormulaArray = myFormula
or to a range of cells using code similar to this:
Worksheets("NameOfSheet").Range("A1202:A1215").FormulaArray = 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
>
>

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").FormulaArray = myFormula
>or to a range of cells using code similar to this:
>Worksheets("NameOfSheet").Range("A1202:A1215").FormulaArray = 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