How to setfocus on modeless form

Rang655

New Member
Joined
Jul 2, 2009
Messages
14
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
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…
Code:
' 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

Code:
' this goes at a regular module


Sub Rang()


    UserForm2.Show vbModeless


End Sub
 

Rang655

New Member
Joined
Jul 2, 2009
Messages
14
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

Well-known Member
Joined
Oct 30, 2011
Messages
4,129
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>
Code:
' this code goes at the sheet module
Private Sub CommandButton1_Click()
    UserForm5.Show vbModeless
End Sub


Code:
' 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

New Member
Joined
Jul 2, 2009
Messages
14
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,130,013
Messages
5,639,553
Members
417,097
Latest member
miguel_z

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top