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

 

[Jaafar Tribak]

Michael.

The class we have built on this thread is by no means perfect so any userform using it will definitly need some trial and error work done and its code will need some tweaking.

I just thought it was a nice idea from Jon to be able to have a generic and reusable Class module which you can use to sink the Enter and Exit events of each form Control within a common event handler. Also a good learning exercise.

So like I said ,do some playing around with the userform code and you will probably get everything running fine.

For example, one way you can probably force the focus on the country_combo Control when first loading the form is by temporarly setting its TabIndex to 0 as follows :

Private iTabIndex As Integer

Private Sub UserForm_Initialize()

    iTabIndex = country_combo.TabIndex
    country_combo.TabIndex = 0

End Sub

Private Sub UserForm_Activate()

    country_combo.SetFocus
    country_combo.TabIndex = iTabIndex

End Sub

You can also carefully pre-arrange the tabIndexes of the form controls at design time via the Properties window to avoid potential focus conflicts.

 

[Jaafar Tribak]

I think you need to set focus to that control in the forms Layout event, before you instatiate the class.

Nice one Jon. easier/cleaner than messing with the TabIndexes as I suggested above

 

[Michael Simpson]

Many thanks for your code Jaafar. I changed an example I had that illustrated my problem to your event code and it worked fine after that.

Nonetheless, I'll see if I can get this reported via our support people at work to MS since it certainly seems to be a bug that has been fixed in 2010

 

[Michael Simpson]

One last problem using your examples Jaafar.

At the moment, I still can't get focus to be in the first control in the form when the form is shown the first time. I then noticed that your startwatching code starts with

Set oPrevActiveCtl = Form.ActiveControl

When debugging, the first time in, this is set to "" and it occurred to me that maybe, if I could get the VBA code to "think" that it had just come from my country_combo, all would be well.

Setfocus on the combo doesn't seem to do the trick. Is there a way of forcing the active control to be considered to be the combo via some VBA code ???

 

[RoryA]

If you are setting focus in the Activate event, try focussing on another control first, then back to your combo. Is its tabindex 0?

 

[Michael Simpson]

I realise that you did say that your code wouldn't cover everything, however ... Using your code, I notice that the UserFormCtl_OnExit doesn't kick in if you have textbox entries within a frame. The only control that kicks in is when you tab out of the last textbox in the frame (and then the value in ctrl is the name of the frame).

Any good way to get round this ????

 

[bolo]

Bump!!!!

I have the same problem as Michael Simpson! anywayaroun theprblem of controls withn frames and multipages?

Many Thanks

 

[CSyx]

Bump!!!!

Same problem here. Has anyone adapted the code from Jaafar (which is awesome) to use it with frames and multipages?

 

[MartinS]

I've been playing with the code kindly supplied by Jaafar, as needed a way to validate textboxes and also count the number of textbox and combobox controls that are changed to ensure users don't lose changes.
I take it the code applies the event to all input controls on the form, which is fine as my code ignores controls that don't have any validation rules. I started off using the example posted in Excel VBA UserForm Enter Event for multiple Comboboxes or TextBoxes, and whilst I need specific code for textboxes in the exit event, i've been able to update my code to deal with this. But I also needed to apply the keypress event - am I better off leaving the keypress handling (which works) under control of the textbox class event handler in the ozgrid example, rather than updating this code to include the keypress event? Or is adding the keypress event simpler than it seems?
Thanks
Martin

 

[MartinS]

Oh and I'm also using frames and multi-pages, so having the same issues i.e. the the exit event won't fire for controls inside frames (though it appears to work quite happily without frames on multi-pages)
Thanks
Martin