How to identify which text box is active in real time?

Question

I have a question about how to get active control's name in real time. Here are the details.

I created a form allows users to input records(please see attached screenshot). Through the form, a user can add Anhui province's natural gas supply and demand figures in 2000. enter image description here

One function I'd like to do is when input certain record, e.g. natural gas "Total Supply" of Anhui in 2000, the form can display all Anhui's natural gas "Total Supply" near 2000 year, say, 1997-1999, in a column chart to give users a reference. When the user edits "Import", the chart will switch to "Import" accordingly.

I'm aware that OnClick() is able to help me get active text boxes' names, but there are too many boxes. I guess there must be some other easy ways. And I also feel I might need another thread to capture the active control's name if I choose not to use OnClick(), but seems multiple threads are not supported in VBA?

Thanks very much for your help in advance!

[Try this](https://stackoverflow.com/q/660173/5267751)? – user202729 Jan 25 '18 at 06:03 — user202729, Jan 25 '18 at 06:03

Sergey S. · Answer 1 · 2018-01-25T06:31:03.773

Create a function in form's module:

Private Function FieldClicked()
    Dim ctl As Control

    Set ctl = Me.ActiveControl
    Select Case ctl.Name
    Case "Field1"
        ....
    Case Else
        ....
    End Select
End Function

In design mode select all text boxes you want to analyse and in On Click property type =FieldClicked(). In this case you won't need to create OnClick event handler for each field - just one function, where you can analyse whatever you want.

BTW, for your purpose it would be better to use On Enter event, not On Click - user can enter to the field using keyboard, not mouse.

score 0 · Accepted Answer · answered Jan 25 '18 at 08:47

If your aim is to avoid declaring an OnEnter event for every textbox then you can dynamically add the events when the form is loaded:

Private Sub Form_Load()
    Dim ctl As Control
    For Each ctl In Me.Controls
        If TypeOf ctl Is Textbox Then
            Dim txtbox As Textbox
            Set txtbox = ctl
            txtbox.OnEnter = "=FieldEntered(""" & txtbox.Name & """)"
        End If
    Next ctl
End Sub

Public Function FieldEntered(textboxName As String)
    Debug.Print textboxName
End Function

You can then do whatever you need to with the chart using the textboxName parameter passed to the FieldEntered function.

You could also just use Me.ActiveControl.Name within the FieldEntered function and not pass it via a parameter but that's mostly aesthetics.

Note: for this to work the FieldEntered function must be a Public Function.

An alternative is using the Form's Timer event but that has performance implications, so I think the above is more robust.

How to identify which text box is active in real time?

2 Answers2