Class Module & GotFocus with ComboBoxes

Hello,

I would like to create a class module to simplify my procedure. These are ComboBoxes that depend on the GotFocus event.
I started, somehow, to deal with the problem, but it didn't work.
How to do it ?

Hi there,

I haven't read your code in detail, but it seems that you're trying to use the GotFocus event in a Class module which contains a ComboBox object declared WithEvents.

Unfortunately (as far as I am aware) the GotFocus event is not exposed in such a situation. When you place the cursor inside your "CBL_Click" routine you can see the list of available events in the dropdown list at the top of the VBA Editor window (GotFocus is not included).

I had a similar requirement some years ago and I found that an excellent solution had been developed by Jaafar Tribak; it can be found using the following link:

http://www.mrexcel.com/forum/excel-q...control-3.html

Hope this helps you - it certainly helped me!

Regards,

Greg M

Hi again,

I looked a bit on other forums, actually it does not seem to be possible.

But I think that it very definitely IS possible!

Take a look at the attached workbook and see if it gets you moving in the right direction. Select/tab through the various controls on the UserForm and see how the ComboBoxes respond when they receive or lose the focus.

The VBA CodeModule for the UserForm contains the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Public WithEvents mclsWatchFormControls As C21_WatchFormControls


'=========================================================================================
'=========================================================================================
'           USERFORM ROUTINES
'=========================================================================================
'=========================================================================================


Private Sub UserForm_Initialize()

    Me.btnClose.SetFocus

End Sub


'=========================================================================================
'=========================================================================================


Private Sub UserForm_Activate()

'   NOTE: The Class Method invoked by this routine accesses the currently-active Control
'   on the UserForm, so the UserForm must already have been created (otherwise there can
'   be no active Control on it) - the following code must therefore be called from the
'   UserForm_Activate (i.e. this) routine rather than from the UserForm_Initialize routine

'   Instantiate the Class which will watch for UserForm controls getting/losing the Focus
    If mclsWatchFormControls Is Nothing Then

        Set mclsWatchFormControls = New C21_WatchFormControls
            mclsWatchFormControls.StartWatching Form:=Me

    End If

End Sub


'=========================================================================================
'=========================================================================================


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

    If CloseMode <> vbFormCode Then

        Call btnClose_Click
        Cancel = True

    End If

End Sub


'=========================================================================================
'=========================================================================================
'           USERFORM CONTROL ROUTINES
'=========================================================================================
'=========================================================================================


Private Sub btnClose_Click()

    Call StopWatchingFormControls

    Me.Hide

End Sub


'=========================================================================================
'=========================================================================================
'           PRIVATE ROUTINES
'=========================================================================================
'=========================================================================================


Private Sub StopWatchingFormControls()

    If Not mclsWatchFormControls Is Nothing Then

        mclsWatchFormControls.StopWatching
        Set mclsWatchFormControls = Nothing

    End If

End Sub


'=========================================================================================
'=========================================================================================
'           CLASS EVENT ROUTINES
'=========================================================================================
'=========================================================================================


Private Sub mclsWatchFormControls_ControlGotFocus(ctl As MSForms.control)

    If TypeOf ctl Is MSForms.ComboBox Then

        MsgBox "The ComboBox """ & ctl.Name & """ has got the focus"

    End If

End Sub


'=========================================================================================
'=========================================================================================


Private Sub mclsWatchFormControls_ControlLostFocus(ctl As MSForms.control, _
                                                   bCancel As Boolean)

    If TypeOf ctl Is MSForms.ComboBox Then

        MsgBox "The ComboBox """ & ctl.Name & """ had lost the focus"

    End If

End Sub

And a Class Module called "C21_WatchFormControls" contains the following code:

Option Explicit


'=========================================================================================
'=========================================================================================


Const msMODULE_NAME             As String = "C21_WatchFormControls"


'=========================================================================================
'=========================================================================================


Public Event ControlLostFocus(ctl As MSForms.control, bCancel As Boolean)
Public Event ControlGotFocus(ctl As MSForms.control)

'~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~

Private mctlPreviouslyActive    As MSForms.control
Private mbFormLoaded            As Boolean
Private mbCancel                As Boolean


'=========================================================================================
'=========================================================================================
'           CLASS METHODS
'=========================================================================================
'=========================================================================================


Public Sub StartWatching(Form As UserForm)

    mbFormLoaded = True

    Set mctlPreviouslyActive = Form.ActiveControl
    RaiseEvent ControlGotFocus(Form.ActiveControl)

    Do While mbFormLoaded = True

        If Not mctlPreviouslyActive Is Nothing Then

            If Not mctlPreviouslyActive Is Form.ActiveControl Then

                RaiseEvent ControlLostFocus(mctlPreviouslyActive, mbCancel)

                If mbCancel Then

                      mctlPreviouslyActive.SetFocus

                Else: RaiseEvent ControlGotFocus(Form.ActiveControl)
                      Form.ActiveControl.SetFocus

                End If

            End If

        End If

        Set mctlPreviouslyActive = Form.ActiveControl

        mbCancel = False
        DoEvents

    Loop

End Sub


'=========================================================================================
'=========================================================================================


Public Sub StopWatching()

    Set mctlPreviouslyActive = Nothing
    mbFormLoaded = False

End Sub

As I mentioned in my original post, I'm happy to acknowledge that the idea behind the above code came from Jaafar Tribak.

By the way, I just love the lists of "ingredients" in your ComboBox dropdown lists.

Hope this helps - please let me know how you get on.

Regards,

Greg M