Cannot SetFocus > userform control

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,907
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
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
 
Depends what you mean. You can create a custom Exit event for your class and raise it instead of calling the code you have now, but you cannot simply create an Exit event for the Textbox object.
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
This is going into very new realms for me, but what I am wondering is possible:

1. Rather than having an events for each type of control (i.e. textbox and combobox), rather just have a generic one for forms controls. E.g:
Code:
Private WithEvents m_ufControl as MSForms.Control

2. Create a custom event (ctlExit), that effectively replaces ControlEventTriggered, and can be associated with m_ufControl.

3. Raise the event whenever the tab key is pressed, or when the user clicks the mouse and the current control loses focus (using the userform events at my disposal).

I think that makes sense... I'm going to have a crack at it, but please let me know if I am hopelessly off-track and shouldn't bother wasting the time on it. :)
 
Upvote 0
If I understand you correctly, yes that should work. It should also make the class more reusable since the consumer of the class can respond to the exit event in whatever way it deems appropriate.
 
Upvote 0
Cool! Something new for me to play with. :)

Thanks again for your help today. I'll post back when I'm done (or when I hit the next brick wall).
 
Upvote 0
No worries - it helps stop me falling asleep at work! :)
 
Upvote 0
Nope - I would still be there!
 
Upvote 0
This is going into very new realms for me, but what I am wondering is possible:

1. Rather than having an events for each type of control (i.e. textbox and combobox), rather just have a generic one for forms controls. E.g:
Code:
Private WithEvents m_ufControl as MSForms.Control
2. Create a custom event (ctlExit), that effectively replaces ControlEventTriggered, and can be associated with m_ufControl.

3. Raise the event whenever the tab key is pressed, or when the user clicks the mouse and the current control loses focus (using the userform events at my disposal).

I think that makes sense... I'm going to have a crack at it, but please let me know if I am hopelessly off-track and shouldn't bother wasting the time on it. :)


Hi jon.

This is an interesting question.

Here is something that would capture both the missing Exit and Enter events of every control on the userform and run the event code in a single event routine located in a Class module.

You can conviniently use the Ctrl argument passed to the cutom event to know which Control raised the event and act accordingly.

UserForm demo.


Code In the UserForm :

Code:
Option Explicit

Public Event OnEnter(Ctrl As MSForms.Control)
Public Event OnExit(Ctrl As MSForms.Control)

Private oXitClass As CtlExitCls
Private bFormUnloaded As Boolean
Private oPrevActiveCtl As MSForms.Control
Private oCol As New Collection

Private Sub UserForm_Layout()

    Call WatchEvents

End Sub

Private Sub UserForm_Terminate()

    Call CleanUp

End Sub


Private Sub WatchEvents()

    If Not oXitClass Is Nothing Then Exit Sub

    Set oXitClass = New CtlExitCls
    Set oXitClass.FormCtrl = Me
    
    bFormUnloaded = False
    
    Set oPrevActiveCtl = Me.ActiveControl
    RaiseEvent OnEnter(Me.ActiveControl)
    
    Do While bFormUnloaded = False
        If Not oPrevActiveCtl Is Nothing Then
            If Not oPrevActiveCtl Is Me.ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtl)
                RaiseEvent OnEnter(Me.ActiveControl)
                Me.ActiveControl.SetFocus
            End If
        End If
        Set oPrevActiveCtl = Me.ActiveControl
        DoEvents
    Loop

End Sub

Private Sub CleanUp()

    bFormUnloaded = True
    RaiseEvent OnExit(oPrevActiveCtl)
    Set oXitClass = Nothing
    Set oCol = Nothing
    Set oPrevActiveCtl = Nothing

End Sub



In a Class module named
( CtlExitCls )

Code:
Option Explicit


Public WithEvents FormCtrl As UserForm1


Private Sub FormCtrl_OnEnter(Ctrl As MSForms.Control)


    FormCtrl.Label2 = "You Entered the Control :  " & "(" & Ctrl.Name & ")"


End Sub


Private Sub FormCtrl_OnExit(Ctrl As MSForms.Control)

    FormCtrl.Label1 = "You left the Control :  " & "(" & Ctrl.Name & ")"
    
    
    'Do stuff according to the Ctrl argument.
    '=======================================
    '    Select Case True
    '
    '        Case Ctrl Is FormCtrl.TextBox1
    '            MsgBox Ctrl.Name & "  was exited"
    '
    '        Case Ctrl Is FormCtrl.TextBox2
    '            MsgBox Ctrl.Name & "  was exited"
    '
    '       ' Case '.......
    '
    '    End Select
End Sub
The code uses a loop so to minimize any interference with any preexisting code , I decided to place the loop inside the Layout Form event.
 
Upvote 0
In keping with the BuitIn Control Exit event Signature , I have amended the previous code to accomodate a Cancel Argument .

The code in the userform becomes as follows : ( Changes are in blue )

Code:
Option Explicit

Public Event OnEnter(Ctrl As MSForms.Control)
Public Event OnExit(Ctrl As MSForms.Control,[COLOR=Blue] [B]Cancel As Boolean[/B][/COLOR])

Private oXitClass As CtlExitCls
Private bFormUnloaded As Boolean
[B][COLOR=Blue]Private bCancel As Boolean[/COLOR][/B]
Private oPrevActiveCtl As MSForms.Control
Private oCol As New Collection

Private Sub UserForm_Layout()

    Call WatchEvents

End Sub

Private Sub UserForm_Terminate()

    Call CleanUp

End Sub


Private Sub WatchEvents()

    If Not oXitClass Is Nothing Then Exit Sub
    
    
    Set oXitClass = New CtlExitCls
    Set oXitClass.FormCtrl = Me
    
    bFormUnloaded = False
    
    Set oPrevActiveCtl = Me.ActiveControl
    RaiseEvent OnEnter(Me.ActiveControl)
    
    Do While bFormUnloaded = False
        If Not oPrevActiveCtl Is Nothing Then
            If Not oPrevActiveCtl Is Me.ActiveControl Then
                RaiseEvent OnExit(oPrevActiveCtl, bCancel)
                RaiseEvent OnEnter(Me.ActiveControl)
                [B][COLOR=Blue]If bCancel Then[/COLOR][/B]
                   [B] [COLOR=Blue]oPrevActiveCtl.SetFocus[/COLOR][/B]
                [B][COLOR=Blue]Else[/COLOR][/B]
                    [B][COLOR=Blue]Me.ActiveControl.SetFocus[/COLOR][/B]
               [COLOR=Blue] [B]End If[/B][/COLOR]
            End If
        End If
        Set oPrevActiveCtl = Me.ActiveControl
        DoEvents
    Loop

End Sub

Private Sub CleanUp()

    bFormUnloaded = True
    RaiseEvent OnExit(oPrevActiveCtl, [B][COLOR=Blue]bCancel[/COLOR][/B])
    Set oXitClass = Nothing
    Set oCol = Nothing
    Set oPrevActiveCtl = Nothing

End Sub
And the Class module code becomes like :

Code:
Option Explicit


Public WithEvents FormCtrl As UserForm1


Private Sub FormCtrl_OnEnter(Ctrl As MSForms.Control)


    FormCtrl.Label2 = "You Entered the Control :  " & "(" & Ctrl.Name & ")"

End Sub

Private Sub FormCtrl_OnExit(Ctrl As MSForms.Control, [B][COLOR=Blue]Cancel As Boolean[/COLOR][/B])

    FormCtrl.Label1 = "You left the Control :  " & "(" & Ctrl.Name & ")"
    
    'Do stuff according to the Ctrl argument.
    '=======================================
    '    Select Case True
    '
    '        Case Ctrl Is FormCtrl.TextBox1
    '            MsgBox Ctrl.Name & "  was exited"
    '
    '        Case Ctrl Is FormCtrl.TextBox2
    '            MsgBox Ctrl.Name & "  was exited"
    '
    '       ' Case '.......
    '
    '    End Select
    
End Sub

You can now code the Exit event in the Class module to prevent leaving the control of your choice.
 
Upvote 0
Hi Jaafar

Thanks for this. It works well. I'm just having a bit more of a play and then I will post back again.

Thanks again. :)
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top