Cannot SetFocus > userform control

[Jon von der Heyden]

Hi All

Posting a rather lengthy class here. Everything works as expected, except the .SetFocus line. Testing on textboxes, the control in question is not selected, yet the error message displays:

clsFormsControlsEvents:

Private WithEvents m_TextBoxEvents As MSForms.TextBox
Private WithEvents m_ComboBoxEvents As MSForms.ComboBox
Private m_blnOk As Boolean 'boolean to tell us whether or not all controls ok

Public Property Set Control(ctlNew As MSForms.Control)
    Select Case TypeName(ctlNew)
        Case "TextBox"
            Set m_TextBoxEvents = ctlNew
        Case "ComboBox"
            Set m_ComboBoxEvents = ctlNew
    End Select
End Property

'there's no exit event, so we use key/mouse down events, check for tab and click to a different control

Private Sub m_TextBoxEvents_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then
        Call ControlEventTriggered(m_TextBoxEvents)
    End If
End Sub

Private Sub m_TextBoxEvents_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

End Sub

Private Sub ControlEventTriggered(ByVal ctl As MSForms.Control)
    Dim varArrTag As Variant
    varArrTag = Split(ctl.Tag, ",")
    'tag: (0) = Mandatory boolean
    '     (1) = Data type
    '     (2) = Length
    
    With ctl
        If varArrTag(0) = True And Len(.Object.Value) = 0 Then
            [COLOR=Red][B].SetFocus[/B][/COLOR]
            MsgBox Prompt:="Mandatory field!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
            m_blnOk = False
            GoTo Finally
        End If
        Select Case varArrTag(1)
            Case "date"
                If Not IsDate(.Object.Value) Then
                    [COLOR=Red][B].SetFocus[/B][/COLOR]
                    MsgBox Prompt:="Invalid date!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
                    m_blnOk = False
                    GoTo Finally
                End If
            Case "numeric"
                If Not IsNumeric(.Object.Value) Then
                    [COLOR=Red][B].SetFocus[/B][/COLOR]
                    MsgBox Prompt:="Number field!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
                    m_blnOk = False
                    GoTo Finally
                End If
            'more cases here
        End Select
        If Len(.Object.Value) > CLng(varArrTag(2)) Then
            [COLOR=Red][B].SetFocus[/B][/COLOR]
            MsgBox Prompt:="Max " & varArrTag(2) & " chars!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
            m_blnOk = False
            GoTo Finally
        End If
    End With
    
    m_blnOk = True
    
Finally:
    Erase varArrTag
End Sub

Private Sub Class_Terminate()
    Set m_TextBoxEvents = Nothing
    Set m_ComboBoxEvents = Nothing
    m_blnOk = Empty
End Sub

 

[Jon von der Heyden]

Bumping back to the top.

I'm using a class to trap userform control events. For now I'm just focussing on textboxes and comboxes. I am using a collection to hold the class instances. When I use the tab key to move to the next control then the event is fired up and calls ControlEventTriggered. When it fails any one of the tests a message box is displayed (works correctly) and it is supposed to set focus back to the control. The SetFocus method isn't working. By not working I mean focus remains on the next control, although no error is raised.

Can anyone shed light on why the SetFocus method won't work?

 

[Michael Simpson]

I'm also puzzled by this. I actually (without seeing this topic) started a similar topic about this today. See http://www.mrexcel.com/forum/showthread.php?t=519618

Long and the short of it - how on earth do you get setfocus to work ??

 

[RoryA]

It's a timing issue. At the time your code runs, the control still has the focus, so setting focus back to it works, but then focus is switched to the next control as a result of pressing the tab key.

 

[Michael Simpson]

and the way round it is ....... ???????

 

[RoryA]

Well, I wouldn't use this method myself, since I think it is better to do this in a validation routine before saving (I'd just highlight the control rather than preventing the user leaving as I find it extremely irritating), but you could set the Keycode to 0 - something like:

Option Explicit

Private WithEvents m_TextBoxEvents As MSForms.TextBox
Private WithEvents m_ComboBoxEvents As MSForms.ComboBox
Private m_blnOk As Boolean 'boolean to tell us whether or not all controls ok

Public Property Set Control(ctlNew As MSForms.Control)
    Select Case TypeName(ctlNew)
        Case "TextBox"
            Set m_TextBoxEvents = ctlNew
        Case "ComboBox"
            Set m_ComboBoxEvents = ctlNew
    End Select
End Property

'there's no exit event, so we use key/mouse down events, check for tab and click to a different control

Private Sub m_TextBoxEvents_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyTab Then
        Call ControlEventTriggered(m_TextBoxEvents, KeyCode)
    End If
End Sub

Private Sub ControlEventTriggered(ByVal ctl As MSForms.Control, ByVal KeyCode As MSForms.ReturnInteger)
    Dim varArrTag As Variant
    varArrTag = Split(ctl.Tag, ",")
    'tag: (0) = Mandatory boolean
    '     (1) = Data type
    '     (2) = Length
    
    With ctl
        If varArrTag(0) = True And Len(.Object.Value) = 0 Then
         MsgBox ctl.Parent.ActiveControl.Name
            KeyCode = 0
            MsgBox Prompt:="Mandatory field!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
            m_blnOk = False
            GoTo Finally
        End If
        Select Case varArrTag(1)
            Case "date"
                If Not IsDate(.Object.Value) Then
                  KeyCode = 0
                    MsgBox Prompt:="Invalid date!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
                    m_blnOk = False
                    GoTo Finally
                End If
            Case "numeric"
                If Not IsNumeric(.Object.Value) Then
                  KeyCode = 0
                    MsgBox Prompt:="Number field!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
                    m_blnOk = False
                    GoTo Finally
                End If
            'more cases here
        End Select
        If Len(.Object.Value) > CLng(varArrTag(2)) Then
            KeyCode = 0
            MsgBox Prompt:="Max " & varArrTag(2) & " chars!", Buttons:=vbExclamation + vbOKOnly, Title:=ctl.Parent.Name
            m_blnOk = False
            GoTo Finally
        End If
    End With
    
    m_blnOk = True
    
Finally:
    Erase varArrTag
End Sub

Private Sub Class_Terminate()
    Set m_TextBoxEvents = Nothing
    Set m_ComboBoxEvents = Nothing
    m_blnOk = Empty
End Sub

 

[Jon von der Heyden]

Thanks Rory.

I had hoped I could use an exit event, but it doesn't seem to be available for MSForms textbox in class module. What's that all about?

From what I can see I can't use KeyPress event because I don't seem to be able to trap the Tab key. And even if I could I suspect the same issue will apply. I tried the KeyUp event and I thought throwing in DoEvents before calling ControlEventTriggered might help, but no joy there.

Have you got any idea how to move focus back?

 

[Jon von der Heyden]

Just saw your reply. I'm going to explore now, thanks.

Oh, and I have argued both the points you made See, I do pay attention to you.

 

[Michael Simpson]

Thank you Rorya. I'll have a good look at it. The reason I want to be able to handle exiting a control is that the one I'm using contains a number of paired enabled/protected entry controls where the first one (enabled) is a currency amount and the second (protected) is the equivalent currency amount in SEK.

I want to be able to convert the foreign currency to SEK as soon as they leave the control.

 

[Jon von der Heyden]

Yup, it makes perfect sense (why didn't I think of that < don't answer that!), and it works a treat!

Thanks Rory