How to get the active field?

[DeepButi]

I have a Form with several Text fields that go to a common code on TextBoxn_Change. How do I get the field that triggers the event so I can call the common routine with it?

 

[Skyybot]

Call a common Sub from the TextBox Sub.

Sub TextBox1_Change() 
Call myTextBoxSub
End Sub
Sub myTextBoxSub()
'Do stuff
End Sub

 

[DeepButi]

Hi, thks, I'm already doing that, but how do I get the field that trigger the change event?

Sub TextBox1_Change()
    call MyTest
End Sub
Sub TextBox2_Change()
    call MyTest
End Sub
Sub TextBox3_Change()
    call MyTest
End Sub
...
Sub TextBox20_Change()
    call MyTest
End Sub


Sub MyTest()
' How do I know the object? its value? etc etc
    if ?????.text = "whatever" then DoSomething
End Sub

 

[Skyybot]

Are your TextBoxes on a Sheet, or Form? Are they ActiveX, or Form Controls?

 

[DeepButi]

Are your TextBoxes on a Sheet, or Form? Are they ActiveX, or Form Controls?

Form controls (although I will appreciate answer for Active-X also)

I need the whole object becaue I use diferent properties ... Call MyTest(Text1.Text) is not enough .... just looking for the ActiveCell equivalent

 

[Skyybot]

If you are using a Form control on a Form, then your code must me written in the Form.

If it's ActiveX in Sheet, then your code must be written in the Sheet.

 

[Skyybot]

 

[DeepButi]

I know where to write the code. Seems I cannot explain the problem correctly ... all answers don't take into account I have dozens of fields (TextBox1 TextBox2 .... TextBox20) . I need to access several of their properties in the common Sub, so I want to know which object fired the event.

Private Sub Text1_sub()
    Call MyTest()
End Sub
Private Sub Text2_sub()
    Call MyTest()
End Sub
Private Sub Text3_sub()
    Call MyTest()
End Sub
Private Sub Text4_sub()
    Call MyTest()
End Sub
...
Private Sub Text20_sub()
    Call MyTest()
End Sub

Private Sub MyTest()
    ' Here I need to access say CurrentField.Text CurrentField.BackColor CurrentField.ForeColor .... etc etc so I need CurrentField
   ' if it were a cell I would use ActiveCell, but I don't know what to use for a Form Field
End Sub[/COLOR]

And I would like to avoid
Call MyTest(Text1)
...
Call MyTest(Text2)
... and so on

Thks

 

[Skyybot]

Try this setup and see if it's pulling the information that you're looking for.
This Code goes in Form

Private Sub TextBox1_Change()
Dim tbName As String, aCell As Range
Set aCell = ActiveCell
tbName = Me.ActiveControl.Name
Me.MyTest aCell, tbName
End Sub


Private Sub TextBox2_Change()
Dim tbName As String, aCell As Range
Set aCell = ActiveCell
tbName = Me.ActiveControl.Name
Me.MyTest aCell, tbName
End Sub




Sub MyTest(ByVal cell As Range, ByVal tb As String)
Dim sht As Worksheet
Set sht = ActiveSheet
MsgBox "Current ActiveCell is " & sht.Name & "!" & cell.Address & " and, the current TextBox is " & tb & ".", vbInformation, "Info Show"
End Sub

This code goes in ThisWorkbook or, Sheet. Run this code with F5. Let me know if the information displayed in the MsgBox is what you're looking for.

Sub FormPullTest()
BlankForm.Show
Unload BlankForm
End Sub

 

[Skyybot]

Once you have the TextBox name, then you can reference it's Properties w/
Me.Controls(tb)