Pass the variable into the sub function

[Guest]

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

 

[Guest]

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

'....

 

[Guest]

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,

 

[Guest]

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.

 

[Bob Phillips]

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)