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").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

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

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

Hopefully this somewhat trivial example will help you understand more about
this and get you a little further in this effort:

http://www.jlathamsite.com/uploads/UserFormExample.xls


"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