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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,183
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,183
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).
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,436
Messages
5,837,230
Members
430,487
Latest member
michaeltusi

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