Identify last selected textbox (added at run-time)

[Jfp87]

Guys,

I have the userform below (sorry it isn't very clear). All controls on the multipage have been added at run-time.

18639

I have managed to get a handle on the textbox events using:

Class module


Public WithEvents oTxt As MSForms.Textbox

Private Sub oTxt_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyDown" & " " & oCtrl.Name
End Sub


Private Sub oTxt_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyPress" & " " & oCtrl.Name
End Sub


Private Sub oTxt_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyUp" & " " & oCtrl.Name
End Sub


Private Sub oTxt_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "MouseDown" & " " & oCtrl.Name
End Sub


Private Sub oTxt_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "MouseUp" & " " & oCtrl.Name
End Sub

Userform module


Private m_arrTextboxes() As New clsTextBox


Private Sub UserForm_Initialize()
Dim oCtrl as MSForms.Control
Dim i as integer


'Create events for all dynamically added textbox controls.
i = 1
On Error Resume Next
For Each oCtrl In Me.Controls
If TypeName(oCtrl) = "TextBox" Then
ReDim Preserve m_arrTextboxes(i)
Set m_arrTextboxes(i).oTxt = oCtrl
i = i + 1
End If
Next oCtrl


End Sub

If the user wants to enter a date into a specific textbox, they can click into the textbox then click the 'dd/mm/yy' command button at the top right of the form which will display a datepicker. My problem is that I can't find a way to identify the last textbox control that was selected in order to enter the date.

Any ideas?

Joe

[Tommy]

Class - modified

Public WithEvents oTxt As MSForms.TextBox

Private Sub oTxt_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyDown" & " " & oCtrl.Name
LastTextBox = oCtrl.Name
End Sub


Private Sub oTxt_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyPress" & " " & oCtrl.Name
LastTextBox = oCtrl.Name
End Sub


Private Sub oTxt_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "KeyUp" & " " & oCtrl.Name
LastTextBox = oCtrl.Name
End Sub


Private Sub oTxt_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "MouseDown" & " " & oCtrl.Name
LastTextBox = oCtrl.Name
End Sub


Private Sub oTxt_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim oCtrl As MSForms.Control
Set oCtrl = oTxt
Debug.Print "MouseUp" & " " & oCtrl.Name
LastTextBox = oCtrl.Name
End Sub
In a module

Public LastTextBox As String
This will give you the name of the last text box that the cursor was in.

[Jfp87]

Tommy, thanks for that.

I tried almost that before and it didn't work for me, but I was putting the public variable in the userform code module. I put it in the main module today and it worked fine.

Why doesn't it work when declared in the userform code module?

Joe

[Tommy]

See here : https://msdn.microsoft.com/en-us/library/office/gg264241.aspx

[Jfp87]

Thanks Tommy.

[gmaxey]

Joe\Tommy

I realize that this thread is marked solved and the solution provided certainly works. I was pondering the question the other day and perplexed that the Public variable couldn't declared in the class either. I realize that it may have looked odd there as a property of the individual textboxes but still I could wiite a correct value to itduring one of the events but when I tried recall it in the userform cmd button click even it returned null. For some reason the variable was falling out of scope between one of the events and clicking the command button. Tommy, I couldn't find an explanation for that in the reference.

Anyway I shared my version of code privately with a friend who sometimes lingers here under the name Frosty. Frosty explained and showed me how to the LastControlName could be declared and used as a public property of the userform.

Move Public LastControlName As String from the standard module of the to the top of the userform module.

Then change the code in your events as per the following example. Joe note that there is no need to declare and set variable oCtrl = oTxt because oTxt is already the control of interest.

Private Sub oTxt_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "KeyDown" & " "; oTxt.Name
oTxt.Parent.LastControlName = oTxt.Name
End Sub

Now any time Frosty schools me on classes his preferred method is using Public variables where possible and generally feels that Get\Let\Set is unnecessary baggage. For whatever reason I then try to fully encapsulate his simple functioning code using private variables. So, with a class named clsTextBoxPrivate:


Option Explicit
Private WithEvents oTextBox As MSForms.TextBox
Private Sub Class_Initialize()
Debug.Print "Initialize"
End Sub
Property Set TextBoxControl(oTextBoxPassed As Object)
Set oTextBox = oTextBoxPassed
End Property
Property Get TextBoxControl()
TextBoxControl = oTextBox
End Property
Private Sub oTextBox_Change()
Debug.Print "Change" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub oTextBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "KeyDown" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub oTextBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Debug.Print "KeyPress" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub oTextBox_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Debug.Print "KeyUp" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub oTextBox_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Debug.Print "MouseDown" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub oTextBox_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Debug.Print "MouseUp" & " "; oTextBox.Name
oTextBox.Parent.LastTextBoxName = oTextBox.Name
End Sub
Private Sub Class_Terminate()
Debug.Print "Terminate"
End Sub


and the following userform code:


Option Explicit
Private m_arrTextboxes() As New clsTextBoxPrivate
Private m_LastTextBoxName As String
Property Get LastTextBoxName() As String
LastTextBoxName = m_LastTextBoxName
End Property
Property Let LastTextBoxName(strName As String)
m_LastTextBoxName = strName
End Property
Private Sub CommandButton1_Click()
MsgBox LastTextBoxName
End Sub
Private Sub UserForm_Initialize()
Dim oCtrl As MSForms.Control
Dim lngIndex As Long
On Error Resume Next
Set oCtrl = Controls.Add("Forms.TextBox.1")
With oCtrl
.Top = TextBox3.Top + TextBox3.Height + 4
.Left = TextBox3.Left
.Width = TextBox3.Width
End With
For Each oCtrl In Controls
If TypeName(oCtrl) = "TextBox" Then
ReDim Preserve m_arrTextboxes(lngIndex)
Set m_arrTextboxes(lngIndex).TextBoxControl = oCtrl
lngIndex = lngIndex + 1
End If
Next oCtrl
End Sub
Private Sub UserForm_Terminate()
Dim lngIndex As Long
For lngIndex = LBound(m_arrTextboxes) To UBound(m_arrTextboxes)
Set m_arrTextboxes(lngIndex) = Nothing
Next lngIndex
End Sub


File with both versions and Frosty's comments attached.

[Tommy]

I was going to say use the calendar control but it isn't available anymore.