How to setfocus on modeless form

[Rang655]

Hi, I'm trying to do data validation on a text box on a modeless form (ShowModal = False) the following code works when ShowModal = True, but due to the interaction I need the form to be ShowModal = False. How do I get around this problem???

Private Sub MazeH_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

With Me.ActiveControl
If .Value < 10 And .Value <> vbNullString Then
MsgBox "Value must be 10 or more", vbCritical, "Input Error"
Cancel = True
.Value = Horz
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)

End If
End With

End Sub

 

[Worf]

Hi
When the sheet has the focus, the form will stay visible but inactive; on click, the text box will receive the focus. If this is not what you want, please explain…

' this code goes at the Form module


Private Sub MazeH_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)


With Me.ActiveControl
    If .Value < 10 And .Value <> vbNullString Then
        MsgBox "Value must be 10 or more", vbCritical, "Input Error"
        Cancel = True
        .Value = "Horz"
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
    End If
End With


End Sub


Sub CheckBox1_Click()
    Unload Me           ' a way out
End Sub


Private Sub UserForm_Click()


    Me.MazeH.SetFocus       ' when focus comes back to UserForm


End Sub

' this goes at a regular module


Sub Rang()


    UserForm2.Show vbModeless


End Sub

 

[Rang655]

Hi Worf, I tried your code but it yielded the same results. Let me explain the scenario better. On the spreadsheet I have a button called setup which brings up the userform that has the MazeH textbox on it. If the user enters say... 2 in the textbox and pressed enter. The msgbox appears with the message. The user clicks on OK the message box disappears and the useform is selected but MazeH (nor any other widget) does not have focus. Oddly the value did change back to the previous value (desired).

Now if I go into the properties of the userform and change showmodal to TRUE, and rerun the scenario. When the user clicks OK on the message box, it disappears. The MazeH textbox has the previous value in it and has focus and is highlighted ready for the user to put in a valid value. This is the desired behavior but I need the userform property showmodal to be set to FALSE. I thought I might be able to change the showmodal on the fly but it is not in the options after typing "userform."

Any other thoughts???

 

[Worf]

Hi</SPAN></SPAN>
The following setup consists of a modeless UserForm with three controls, with the corresponding tab indexes: CheckBox1 (0), TextBox3 (1) and TextBox1 (2).</SPAN></SPAN>
I tested it with Excel 2003 and it behaved as desired, after the data validation error message the focus returned to the correct text box, without any clicking or typing by the user.</SPAN></SPAN>
If you need, I can post a link to my test workbook.

</SPAN>

' this code goes at the sheet module
Private Sub CommandButton1_Click()
    UserForm5.Show vbModeless
End Sub

' this code goes at the UserForm module  <===
Private Sub textbox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
With Me.ActiveControl
    If .Value < 10 And .Value <> vbNullString Then
        MsgBox "Value must be 10 or more", vbCritical, "Input Error"
        Cancel = True
        .Value = "Default"
        .SelStart = 0
        .SelLength = Len(.Text)
    End If
End With
SendKeys "{TAB}": SendKeys "{TAB}": SendKeys "{TAB}"
End Sub
Sub CheckBox1_Click()   ' another way to close the form
    Unload Me
End Sub
Private Sub UserForm_Activate()
    TextBox3.SetFocus
    TextBox1.SetFocus
End Sub
Private Sub UserForm_Click()
    TextBox3.SetFocus
    TextBox1.SetFocus
End Sub

</SPAN>

 

[Rang655]

Yeah, if you would post me the link. I'm using Excel 2010 so it wouldn't be the first time things that use to work don't work the same way. Case in point using Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long to play embedded sound files but I think I figured that one out (but need more testing).

 

[Worf]

Here is the link, the sheet with the command button is named Rang:

http://dl.dropbox.com/u/52116836/UserForms and Controls_2003.xls