Consulting

Results 1 to 7 of 7

Thread: Identify last selected textbox (added at run-time)

  1. #1
    VBAX Regular
    Joined
    Jul 2014
    Posts
    79
    Location

    Identify last selected textbox (added at run-time)

    Guys,

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

    UFPic.png

    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
    Attached Images Attached Images

  2. #2
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    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.

  3. #3
    VBAX Regular
    Joined
    Jul 2014
    Posts
    79
    Location
    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

  4. #4
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location

  5. #5
    VBAX Regular
    Joined
    Jul 2014
    Posts
    79
    Location
    Thanks Tommy.

  6. #6
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,334
    Location
    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.
    Attached Files Attached Files
    Greg

    Visit my website: http://gregmaxey.com

  7. #7
    Moderator VBAX Master Tommy's Avatar
    Joined
    May 2004
    Location
    Houston, TX
    Posts
    1,184
    Location
    I was going to say use the calendar control but it isn't available anymore.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •