I am creating a UserForm for users to complete to create a new account. I have the following boxes in my form:

AccTypeCodeComboBox
AccTypeDescrTextBox
AccNumberTextBox
AccDescrTextBox

The code below runs on exiting the AccNumberTextBox.

The line "ChkType = AccTypeDescrComboBox.Value" results in a Compile Error: Variable not found - "AccTypeDescrComboBox" is highlighted.

The line "ChkNr = AccNumberTextBox.Value" results in a Run-time error 13: Type mismatch - "AccNumberTextBox" is highlighted.

The runtime error only occurred after I added the section starting at "If IsNull(Me.AccNumberTextBox.Value) Then". Before that there were no runtime error.

I need help with:

1) As what must I define the variable "AccTypeDescrComboBox"?

2) How do I check that "AccNumberTextBox is not empty?

I would appreciate any suggestions you may have.


Option Explicit

Private Sub AccNumberTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Dim ChkType As String
Dim ChkNr As Long
Dim Ulimit As Long
Dim Llimit As Long
Dim i As Integer
Dim lRow As Long

ChkType = AccTypeDescrComboBox.Value
ChkNr = AccNumberTextBox.Value
Ulimit = Application.WorksheetFunction.VLookup(AccTypeCodeComboBox.Value, Sheets("Lists").Range("A7:D19"), 4, False)
Llimit = Application.WorksheetFunction.VLookup(AccTypeCodeComboBox.Value, Sheets("Lists").Range("A7:D19"), 3, False)

If IsNull(Me.AccNumberTextBox.Value) Then
  MsgBox ("You must enter a Number in this field")
  AccNumberTextBox.SetFocus
  Exit Sub
End If

If Not IsNumeric(AccNumberTextBox.Value) Then
  MsgBox ("You must enter a Number in this field")
  AccNumberTextBox.SetFocus
  Exit Sub
End If

If ChkNr < Llimit Or ChkNr > Ulimit Then
  MsgBox ("You have entered an Account Number that is not in the allowable range for the Type of Account you have selected. For an Account Type of " & ChkType & ", you must enter an Account number between " & Llimit & " and " & Ulimit & ". Please enter a new Account Number.")
  AccNumberTextBox.Value = ""
  AccNumberTextBox.SetFocus
  Exit Sub
End If

Range("B12").Select
lRow = ActiveCell.CurrentRegion.Rows.Count + 11
For i = 12 To lRow
  If ChkNr = Range("D" & i).Value Then
    MsgBox ("You have entered an Account Number which already exists!")
    AccNumberTextBox.SetFocus
    Exit Sub
  End If
Next i

End Sub