Userform Change Focus On Field Exit

[Nadine67]

Hello and thank you for any attention my post may receive.

There are two fields on my userform which are the focus of this process - ComboBox1 and TextBox1. ComboBox1 contains Accountable Leader roles.

The code is assigned to TextBox1 - upon exit of TextBox1 the code triggers and validates two things - 1. IF ComboBox1 has a value or not, 2. IF ComboBox1 does a value, check whether TextBox1 value already exists on ws 'Project_List' for the role selected in ComboBox1.

My aim is to trigger the code whenever the user exits TextBox1 by one of three methods - ENTER, TAB, or the user clicks out. So far my code triggers on ENTER and TAB; I am unsure how to achieve my third aim.

This is my code so far:

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 13:
            If Me.ComboBox1.value = "" Then
                temp = MsgBox("You must select the appropriate 'Accountable Leader' first.", vbCritical + vbOKOnly, "You're in the wrong field!")
                Me.TextBox1 = vbNullString
                KeyCode = 0
                ComboBox1.SetFocus
            Else
                With Worksheets("Project_List")
                    If (WorksheetFunction.CountIf(.Columns(4), Me.TextBox1.value) _
                        + WorksheetFunction.CountIf(.Columns(3), Me.ComboBox1.value)) > 0 Then
                        MsgBox "Specific location '" & Me.TextBox1.value & "' already exists for '" & Me.ComboBox1.value & "'..."
                        TextBox1 = vbNullString
                        KeyCode = 0
                    End If
            End With
            End If
            
        Case 9:
            If Me.ComboBox1.value = "" Then
                temp = MsgBox("You must select the appropriate 'Accountable Leader' first.", vbCritical + vbOKOnly, "You're in the wrong field!")
                Me.TextBox1 = vbNullString
                KeyCode = 0
                ComboBox1.SetFocus
            Else
                With Worksheets("Project_List")
                    If (WorksheetFunction.CountIf(.Columns(4), Me.TextBox1.value) _
                        + WorksheetFunction.CountIf(.Columns(3), Me.ComboBox1.value)) > 0 Then
                        MsgBox "Specific location '" & Me.TextBox1.value & "' already exists for '" & Me.ComboBox1.value & "'..."
                        TextBox1 = vbNullString
                        KeyCode = 0
                    End If
                End With
            End If
    End Select
End Sub

Any help or advice would be greatly appreciated.

Have a great day!

 

[Norie]

Why not use the Exit event of the textbox?

 

[Nadine67]

Hi Norie

Great idea Norie......I did start with an Exit event, however I didn't know how to write it to recognize all three exit methods.

Any suggestion Norie?

 

[Norie]

Nadine

You shouldn't need to write it to recognize different exit methods, the Exit event is trigged, well, when the textbox is exited.

 

[Nadine67]

Thank you for your advice Norie.

As you suggested, I used an 'Exit' function and received the result I was after, however my code is returning the same messagebox twice. Any thoughts as to why the code below is returning the messagebox (in the first IF statement) twice?

This is my new code:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.ComboBox1.value = "" Then
        temp = MsgBox("You must select the appropriate 'Accountable Leader' first.", vbCritical + vbOKOnly, "You're in the wrong field!")
        Me.TextBox1 = vbNullString
        ComboBox1.SetFocus
    End If
    If Me.ComboBox1.value <> "" Then
        With Worksheets("Project_List")
            If (WorksheetFunction.CountIf(.Columns(4), Me.TextBox1.value) _
                + WorksheetFunction.CountIf(.Columns(3), Me.ComboBox1.value)) > 0 Then
                MsgBox "Specific location '" & Me.TextBox1.value & "' already exists for '" & Me.ComboBox1.value & "'..."
                TextBox1 = vbNullString
            End If
        End With
    End If
End Sub

 

[Norie]

When you settled focus on ComboBox1 you exit TextBox1 and therefore trigger therefore Exit event again.

Why are you checking if something is entered/selected in the combobox in the Exit event if the textbox?

 

[Nadine67]

I want the user to select a role in ComboBox before they move to the TextBox. If they do this then I want to validate that the text entered in the TextBox is not a duplicate of an entry on ws Project_List.

Perhaps I need separate code that if the ComboBox is blank then the user cannot move to any other field.

 

[Nadine67]

This is the code I finished up with and it returns the results I am after. Thank you for your help and attention Norie.

[CODEPrivate Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsNull(Me.ComboBox1.value) Or Me.ComboBox1.Text = "" Then
MsgBox "You must select the appropriate 'Accountable Leader' first.", vbCritical + vbOKOnly, "You're in the wrong field!"
Cancel = True
End If
End Sub


Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Me.ComboBox1.value <> "" And TextBox1.value <> "" And (WorksheetFunction.CountIf(Worksheets("Project_List").Columns(4), Me.TextBox1.value) _
+ WorksheetFunction.CountIf(Worksheets("Project_List").Columns(3), Me.ComboBox1.value)) > 0 Then
MsgBox "Specific location '" & Me.TextBox1.value & "' already exists for '" & Me.ComboBox1.value & "'..."
TextBox1 = vbNullString
TextBox1.SetFocus
Cancel = True
End If
End Sub
[/CODE]