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

 

[MartinS]

...though it appears to work quite happily without frames on multi-pages

Actually, this isn't true - it doesn't work with controls on a multipage either! I have removed all my frames but still not getting it to work as it does on a form without frames and multipages.
I might have to rethink using this code, though the other option of going back to having events for every control isn't ideal!
Thanks
Martin

 

[gearbawx]

its not hard to change this code to work with frames, as i just pointed out in this thread: http://www.mrexcel.com/forum/excel-...ter-exit-events-not-working-within-frame.html

I have run into my own little problem where the layout event doesnt seem to be working properly. If i set the .Left and .Top of a control it sets it in the object but will not move the control. The only way around this i have found is to set the left/top before the watch code is run.

I have tried disabling the watching before and and re enabling it after setting .Top and .Left but it doesnt seem to work either. It would be able to dynamically move controls while the watch loop is running

Has anyone else run into this problem?

Phill

 

[gearbawx]

Ok this one has beaten me normally i can hack my way around these sorts of problems

I can disable the watching, and then move the control but i cannot disable, move and reenable in the same function

the only way i can re enable the watching is by adding a function call to EVERY control event calling watchevents to start it back up again ( or call ctrl.Move Layout:=true) after the current thread has terminated but I cannot do it in the same execution thread (i even tried some sort of trick with ontime but that doesnt work in userforms :/)

Oh well

 

[M40wen]

Hello,

I have been following this thread for a couple of weeks, and I am now stuck! Basically when I close the userform it looks like there is something still running in memory. Is it possible to close userform, read a spreadsheet, and then create a NEW userform? At the moment when I unload it seems to crash

Cross post here with attachment of what I'm trying to achieve

Dynamic Userform Design - Page 3

 

[Jon von der Heyden]

It seems you have managed to enlist Rory's help on your original thread, so I would progress it there and reply to his questions. You're in good hands.

 

[phillfri]

Actually ontime will work in a userform if the routine you reference with ontime is located in a normal module.

 

[Gon_Incognito]

Hi I figured out a fix for getting this to work in a frame, Mind you 8 years later than you needed it.

Private Sub UserForm_Layout()
If UserFormCtl Is Nothing Then
Set UserFormCtl = New WatchEventsCls
UserFormCtl.StartWatching Me."Frame Name Here"
End If
End Sub

 

[Jaafar Tribak]

I am sure after many years since this thread was started, we have come to know a better alternative for dynamically sinking the Enter,Exit,BeforeUpdate and AfterUpdate events at runtime in a class module without the need to use the WithEvents declartion keyword required at design time.

Here is an example for the record:

Check textbox for future date

Good day again I would like to when user has entered a date (in any format) inside a textbox that when exiting the textbox it checks whether the date is in the future and then displays a message. Also would it be then able to when exiting that the date be changed to show as format 12-Mar-20...

www.mrexcel.com

 

[Gon_Incognito]

Hey Jaafar, I am having an issue with that code, i have left a comment on that thread,

please advise