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
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
3,802
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
3,802
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).
 

Forum statistics

Threads
1,081,793
Messages
5,361,325
Members
400,627
Latest member
Mcomeaux

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top