Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass the variable into the sub function
Hello,
Hope some can help me here. As you can see the example below, I want to write the Sub function only once and able to run the sub function based which txtbox has been updated by a user. Assume X is the variable of the text box. Can you help me to update "Private Sub txtbox"X"_AfterUpdate()" code, please? Thank you for your help and time. Example I have 10 text boxes, txtbox1, txtbox2, txtbox3,.....txtbox9, and txtbox10. Private Sub txtbox"X"_AfterUpdate() intLnumber = X ' I want to run some coding here based which txtbox has been updated. End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass the variable into the sub function
Hi,
AFAIK, its not possible to have a dynaimic function, but you can do this Private Sub gentxtbox(byref X as integer, tbox as textbox) intLnumber = X '.... the tbox is optional but it allows you to access the textbox directly if tbox.vlaue="Hello, World" then msgbox "Hello!" 'or you can just use the number as in if me.controls("txtbox" & X).vlaue="Hello, World" then msgbox "Hello!" End Sub Private Sub txtbox1_AfterUpdate() gentxtbox(1, me.txtbox1) end sub Private Sub txtbox2_AfterUpdate() gentxtbox(2, me.txtbox2) end sub '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Richard" wrote: Hello, Hope some can help me here. As you can see the example below, I want to write the Sub function only once and able to run the sub function based which txtbox has been updated by a user. Assume X is the variable of the text box. Can you help me to update "Private Sub txtbox"X"_AfterUpdate()" code, please? Thank you for your help and time. Example I have 10 text boxes, txtbox1, txtbox2, txtbox3,.....txtbox9, and txtbox10. Private Sub txtbox"X"_AfterUpdate() intLnumber = X ' I want to run some coding here based which txtbox has been updated. End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass the variable into the sub function
Martin,
If dynamic function is not work, then I have to write them for tem times to capture the any update in each text box. I wasn't 100 % sure about your coding. Anyhow, thank you so much for trying to help me here. thanks again, "Martin Fishlock" wrote: Hi, AFAIK, its not possible to have a dynaimic function, but you can do this Private Sub gentxtbox(byref X as integer, tbox as textbox) intLnumber = X '.... the tbox is optional but it allows you to access the textbox directly if tbox.vlaue="Hello, World" then msgbox "Hello!" 'or you can just use the number as in if me.controls("txtbox" & X).vlaue="Hello, World" then msgbox "Hello!" End Sub Private Sub txtbox1_AfterUpdate() gentxtbox(1, me.txtbox1) end sub Private Sub txtbox2_AfterUpdate() gentxtbox(2, me.txtbox2) end sub '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Richard" wrote: Hello, Hope some can help me here. As you can see the example below, I want to write the Sub function only once and able to run the sub function based which txtbox has been updated by a user. Assume X is the variable of the text box. Can you help me to update "Private Sub txtbox"X"_AfterUpdate()" code, please? Thank you for your help and time. Example I have 10 text boxes, txtbox1, txtbox2, txtbox3,.....txtbox9, and txtbox10. Private Sub txtbox"X"_AfterUpdate() intLnumber = X ' I want to run some coding here based which txtbox has been updated. End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass the variable into the sub function
Richard I was trying to show two possible ways of solving your requirements.
Either by simply passing a number or passing the number and the reference to the textbox so that you could process data on the text box depending on which button you pressed. ' typo not vlaue but value!!! me.controls("txtbox" & X).value allows you to refence the text box txtboxX where X is a number whereas ' typo not vlaue but value!!! tbox.value passed a link to the actual textbox and you could access that textbox. It really depends on what other things you want your code to do. I find it easy to write code bits as call them repeatedly if they are doing the same thing and I use either method above depending on the requirments. Have a play with them and see how it goes. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Richard" wrote: Martin, If dynamic function is not work, then I have to write them for tem times to capture the any update in each text box. I wasn't 100 % sure about your coding. Anyhow, thank you so much for trying to help me here. thanks again, "Martin Fishlock" wrote: Hi, AFAIK, its not possible to have a dynaimic function, but you can do this Private Sub gentxtbox(byref X as integer, tbox as textbox) intLnumber = X '.... the tbox is optional but it allows you to access the textbox directly if tbox.vlaue="Hello, World" then msgbox "Hello!" 'or you can just use the number as in if me.controls("txtbox" & X).vlaue="Hello, World" then msgbox "Hello!" End Sub Private Sub txtbox1_AfterUpdate() gentxtbox(1, me.txtbox1) end sub Private Sub txtbox2_AfterUpdate() gentxtbox(2, me.txtbox2) end sub '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Richard" wrote: Hello, Hope some can help me here. As you can see the example below, I want to write the Sub function only once and able to run the sub function based which txtbox has been updated by a user. Assume X is the variable of the text box. Can you help me to update "Private Sub txtbox"X"_AfterUpdate()" code, please? Thank you for your help and time. Example I have 10 text boxes, txtbox1, txtbox2, txtbox3,.....txtbox9, and txtbox10. Private Sub txtbox"X"_AfterUpdate() intLnumber = X ' I want to run some coding here based which txtbox has been updated. End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass the variable into the sub function
You can simulate a control array like so.
First, add a class module, name it clsFormEvents, and insert this code Option Explicit Public WithEvents ctlTB As MSForms.TextBox Private Sub ctlTB_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then 'Enter or Tab MsgBox ctlTB.Name End If End Sub Then in your userform add this code Dim colTB As Collection Private Sub UserForm_Initialize() Dim ctl As Control Dim cEvents As clsFormEvents 'Go through the checkboxes and add them to the frame Set colTB = New Collection For Each ctl In Me.Controls If TypeName(ctl) = "TextBox" Then Set cEvents = New clsFormEvents Set cEvents.ctlTB = ctl colTB.Add cEvents End If Next ctl End Sub Unfortunately, this technique does not give you a textbox AfterUpdate, or even Exit event. What I have done is to test the KeyDown event and check for Enter or Tab and then display the name of te textbox being worked in. Play with it and see if you can use it. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Richard" wrote in message ... Hello, Hope some can help me here. As you can see the example below, I want to write the Sub function only once and able to run the sub function based which txtbox has been updated by a user. Assume X is the variable of the text box. Can you help me to update "Private Sub txtbox"X"_AfterUpdate()" code, please? Thank you for your help and time. Example I have 10 text boxes, txtbox1, txtbox2, txtbox3,.....txtbox9, and txtbox10. Private Sub txtbox"X"_AfterUpdate() intLnumber = X ' I want to run some coding here based which txtbox has been updated. End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pass variable to NORMINSV function to get only mean value | Excel Worksheet Functions | |||
Pass variable to macro | Excel Programming | |||
Pass a variable into a range? | Excel Programming | |||
Can't pass publically defined variable to a function | Excel Programming | |||
Pass a Variable List Of Values To Subroutine or Function | Excel Programming |