Don't unload userform until user enters correct value

[gr8giz]

I have a userform which has several textboxes. I want to ensure that user doesn't leave the fields blank. Hence I have a code to check whether text field is blank when user clicks OK cmdbutton.

Private Sub cb_OK_Click()
    AllClear_Token = False
    If LB_BW.Text = "" Then Call LB_BW_AfterUpdate
   .....
    If AllClear_Token = True Then Unload Input_Parameters
End Sub

In the LB_BW_AfterUpdate() event handler, I have following code:

Private Sub LB_BW_AfterUpdate()
On Error Resume Next
Select Case CDbl(LB_BW.Text)
    Case 1.4
        BW = 1.4
    Case 3
        BW = 3
    Case 5
        BW = 5
End Select
If Err <> 0 Then
    MsgBox "Please select valid Bandwidth"
    Input_Parameters.LB_BW.SetFocus
Else
    AllClear_Token = True
End If
On Error GoTo 0
End Sub

In case a field is left blank I want the userform to stay up and user should be able to correct mistakes or enter text and then click OK again.

However, when looping through the code, when a field is first left blank, code works and user is prompted to enter a value. But when user updates the textbox and clicks OK again, the userform just stays up. It seems at this point that sub is exited already. How do I make it work so that user can enter text in textbox and be able to click Ok again?

 

[GTO]

Hi there,

If LB_BW.Text = "" Then Call LB_BW_AfterUpdate

This is not making sense to me. If the textbox's current value is vbNullString, why call the AfterUpdate event? We already know that it will fail.

If using textboxes, I would look at BeforeUpdate to see if I can stop bad vals from being entered before Exiting the box.

In this case, at least as shown, as there are only three specific values being looked for, why not use a listbox or combobox drop-down?

Mark

 

[gr8giz]

Hello GTO,

Thanks for the comments. I am already using listbox for this particular case (Sorry I didn't select a good example here), but if user forgets to select any option, I want to check that possibility. Also, I have other textboxes which require checking for "Empty field".

Can you explain the below statement a bit more?
" If using textboxes, I would look at BeforeUpdate to see if I can stop bad vals from being entered before Exiting the box."

What is the sequence of events here? When a user starts entering text, does the BeforeUpdate event occur?

 

[GTO]

Hello GTO,

Thanks for the comments. I am already using listbox for this particular case (Sorry I didn't select a good example here), but if user forgets to select any option, I want to check that possibility. Also, I have other textboxes which require checking for "Empty field".

Can you explain the below statement a bit more?
" If using textboxes, I would look at BeforeUpdate to see if I can stop bad vals from being entered before Exiting the box."

What is the sequence of events here? When a user starts entering text, does the BeforeUpdate event occur?

From VBA Help:

Remarks
The AfterUpdate event occurs regardless of whether the control is bound (that is, when the RowSource property specifies a data source for the control). This event cannot be canceled. If you want to cancel the update (to restore the previous value of the control), use the BeforeUpdate event and set the Cancel argument to True.
The AfterUpdate event occurs after the BeforeUpdate event and before the Exit event for the current control and before the Enter event for the next control in the tab order.

By example, and I am afraid not super-well thought through, lets say that you had two textboxes and two comboboxes:

Option Explicit
    
Private Sub cmdOK_Click()
Dim strMsg As String
    
    Select Case True
    Case txtFName.Value = vbNullString
        strMsg = "You must enter a first name"
    Case txtLName.Value = vbNullString
        strMsg = "You must enter a last name"
    Case cboDoubleVals.Value = vbNullString
        strMsg = "You mustr enter a value in the first combobox"
    Case cboLongVals.Value = vbNullString
        strMsg = "You must select a value in the second combobox"
    Case Else
        '// If everything is hunky-dory, unload.    //
        Unload Me
    End Select
    
    '// Else display the msg                        //
    If Not strMsg = vbNullString Then
        MsgBox strMsg, vbCritical, vbNullString
    End If
End Sub
    
Private Sub txtFName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    '// Test to see if there is anything in the textbox, does it meet  our standard? (in this   //
    '// case, letters only)                                                                     //
    If CBool(Len(txtFName.Value)) And Not NameIsAllLettersOrSpaces(txtFName.Value, False) Then
        Cancel = True
        txtFName.Value = vbNullString
        MsgBox "You must enter a first name made up of letters only.", vbInformation, vbNullString
    End If
End Sub
    
Private Sub txtLName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    
    If CBool(Len(txtLName.Value)) And Not NameIsAllLettersOrSpaces(txtLName.Value, True) Then
        Cancel = True
        txtLName.Value = vbNullString
        MsgBox "You must enter a first name made up of letters, spaces, or hyphens.", vbInformation, vbNullString
    End If
End Sub
    
Private Sub UserForm_Initialize()
    
    With Me
        With .cboDoubleVals
            .List = Array(1.4, 3, 5)
            .Style = fmStyleDropDownList
        End With
        With .cboLongVals
            .List = Array(10, 20, 30, 40, 50)
            .Style = fmStyleDropDownList
        End With
    End With
End Sub
    
Private Function NameIsAllLettersOrSpaces(ByVal TextBoxText As String, _
                                          ByVal SpacesHyphensAllowed As Boolean _
                                          ) As Boolean
Static REX As Object
    
    If REX Is Nothing Then Set REX = CreateObject("VBScript.RegExp")
    With REX
        .Global = True
        .IgnoreCase = True
        .Pattern = IIf(SpacesHyphensAllowed, "[^A-z\ \-]", "[^A-z]")
        NameIsAllLettersOrSpaces = Not .Test(TextBoxText)
    End With
End Function

Hope that helps,

Mark

 

[gr8giz]

Thanks, I will try your suggestions and keep posted on how this goes.

 

[gr8giz]

Thank you, using BeforeUpdateEvent instead of Afterupdate did the trick.

 

[GTO]

Thank you, using BeforeUpdateEvent instead of Afterupdate did the trick.

Happy to help and thank you for the follow-up