Made null textbox and set focus on another

Question

I'm using code below, for check another textbox value when exiting initial textbox and if it null, making initial one null and set focus on final textbox.

But i give this error: Run-time error'-2147467259(80004005)': Unspecific error.

when i made comment this line (txtTimeUnit = vbNullString), macro code works correctly.

whats the problem of that line's command and please help me correcting code.

Private Sub txtTimeUnit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtStartDate.Text = vbNullString Then
        txtTimeUnit = vbNullString
        txtStartDate.SetFocus
        Exit Sub
    End If
End Sub

Answer 1

Like I said your code works. Here is an example

Private Sub txtTimeUnit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If txtStartDate.Text = vbNullString Then
        txtTimeUnit.Text = vbNullString
        txtStartDate.SetFocus
        Exit Sub
    End If
End Sub

The only way it will not work is when there is another piece of code which is setting the Cancel = True. For example

Private Sub txtTimeUnit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsError(Application.Match(txtTimeUnit.Text, Range("intTable[Time Unit]"), 0)) Then
        Cancel = True
    End If

    If txtStartDate.Text = vbNullString Then
        txtTimeUnit.Text = vbNullString
        txtStartDate.SetFocus
        Exit Sub
    End If
End Sub

To prevent such kind of errors you can use a Boolean Variable

Dim boolOnce As Boolean

Private Sub txtTimeUnit_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If boolOnce = False Then
        boolOnce = True
        If IsError(Application.Match(txtTimeUnit.Text, Range("intTable[Time Unit]"), 0)) Then
            Cancel = True
        End If
    Else
        boolOnce = False
    End If

    If txtStartDate.Text = vbNullString Then
        txtTimeUnit.Text = vbNullString
        txtStartDate.SetFocus
        Exit Sub
    End If
End Sub