Unable to close form with existing code creating a no go loop

[ipbr21054]

I am using the code below.
Textbox2 will not allow you you contine to next textbox / combobox unless its criteria is met.
So should i wish to use my close button code shown below i am unable to close the userform as the criteria code keeps advising me all the time

Private Sub CommandButton2_Click()
Unload McListForm
Range("A8").Select
End Sub

Private Sub CommandButton1_Click()
      If OptionButton1.Value = True And OptionButton7.Value = False And OptionButton8.Value = False _
      And OptionButton9.Value = False And OptionButton10.Value = False And OptionButton11.Value = False Then
     
     MsgBox "You Must Select A Lead Type", vbCritical, "Lead Type Selection Error Message"
Else
    
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MCLIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
      If ComboBox3.Value = "HONDA" Then
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
      .Cells(8, 9).Font.Color = vbRed
      Else
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
      .Cells(8, 9).Font.Color = vbBlack
      End If
      
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
      If ComboBox3.Value = "HONDA" Then
      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next
      End If
      
      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "DATABASE HAS NOW BEEN UPDATED", vbInformation, "SUCCESSFUL MESSAGE"
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  Else
    MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
    TextBox2.SetFocus
  End If
  End If
      If Me.ComboBox3.Value = "SUZUKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
    Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Offset(, 8).Select
End Sub

 

[Micron]

Hard to follow that because it's not properly indented. My suggestion would be to raise a msgbox; perhaps with Yes No or OK Cancel options or whatever suits you. This would allow user to correct situation or close the form. If to be corrected, you use Exit Sub. If the choice is to close the form anyway, code just continues. The line to close the form should be outside of any If block, from what I can see.

 

[ipbr21054]

Hi,
Are you able to advise what i have done wrong here.

I have it so if the length count is less than 17 show msgbox asking to edit vin or not
Yes will setfocus on textbox2 where no will close userform.

Current i type a few figures in textbox2 & then select next textbox but i shouldnt be abl;e to do that as the count is less than 17 so should have seen the message.

Private Function ExitFunc() As Integer

If Len(Me.TextBox2.Value) < 17 Then
    MsgBox "VIN MUST BE 17 CHARACTERS IN LENGTH" & vbCr & vbCr & "CONTINUE TO EDIT THE VIN ?", vbYesNo + vbCritical
    
If vbYes Then
   TextBox2.SetFocus
   
Else

   Unload McListForm

End If
End If
TextBox2 = UCase(TextBox2)

End Function

 

[Micron]

I thought your validation trigger was the command button that attempts to close the form. If you want validation to occur when user leaves the textbox, you need to use the control's BeforeUpdate event. Or if sticking with your own code for this, you need to Exit Sub if user says yes, otherwise the rest of that code will run. However, I can't tell what calls ExecFunc so when it has completed, control will pass back to the procedure that called it. If it doesn't react correctly at that point, things that you don't want to have happen, will. You're making code reading difficult by not properly indenting. The opening and closing of different blocks should never be indented the same

End If
End If

 

[ipbr21054]

I’m sorry but I have learning difficulties so things don’t also happen like they should.

Can you advise the correct code please for when the user leaves the textbox as I think that’s best.

 

[ipbr21054]

THis works for me

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(TextBox2.Value) <> 17 Then
        Cancel = True
        MsgBox "VIN MUST BE 17 CHARACTERS IN LENGTH" & vbNewLine & vbNewLine & "PLEASE CHECK & TRY AGAIN", vbCritical, "VIN NUMBER LENGTH MESSAGE"
    
    End If
End Sub

 

[Micron]

Glad to see you solved it. Exit event is probably just as good for this but I think the difference would be that BeforeUpdate would keep you on the textbox whereas Exit will not.