Cannot SetFocus > userform control

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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. :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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).
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
No worries - it helps stop me falling asleep at work! :)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,966
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Nope - I would still be there!
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,842
Office Version
2016
Platform
Windows
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.
 

Jaafar Tribak

Well-known Member
Joined
Dec 5, 2002
Messages
7,842
Office Version
2016
Platform
Windows
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.
 

Jon von der Heyden

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,798
Office Version
365
Platform
Windows
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. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,099,055
Messages
5,466,307
Members
406,474
Latest member
osama beskales

This Week's Hot Topics

Top