Advice to alter current prolematic code on userform

[ipbr21054]

I have a userform where one of my Comboboxes must allow either of the following.
N/A from drop down selection or a 17 character value.

The user should be able to select N/A from the drop down & move on but at present the code sees it as not 17 characters so keeps popping up the Msgbox & even when i try to close the form the Msg keeps popping etc but after a while it does close.

If the user doesnt select the N/A then they must enter a 17 character value, Example JHMDG12345TE210258

Code currently in use for ComboBox9 BUT think possibly a new version is needed for it to work correctly
Thanks

Private Sub ComboBox9_Change()
    ComboBox9 = UCase(ComboBox9)
End Sub
Private Function ExitFunc() As Integer

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

End Function
Private Sub ComboBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(ComboBox9.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

 

[Jeffrey Mahoney]

Please try changing this line accordingly

If Len(Me.ComboBox9.Value) < 17 and Me.ComboBox9.Value <> "N/A" Then

 

[Joe4]

Jeffrey,
If you want to use formatting, you must use the Rich code tags, not the VBA ones (formatting does not render properly within VBA code tags).
I updated your post for you.

 

[Jeffrey Mahoney]

Thanks Joe

 

[ipbr21054]

Please try changing this line accordingly

If Len(Me.ComboBox9.Value) < 17 and Me.ComboBox9.Value <> "N/A" Then

Still i get the Msgbox pop up advising it must be 17 characters

 

[Jeffrey Mahoney]

There are two locations where you evaluate the length of the control. Change this:
If Len(ComboBox9.Value) <> 17 Then
To This:
If Len(ComboBox9.Value) <> 17 and ComboBox9.Value <> "N/A" Then

 

[ipbr21054]

ok ive changed it to the following.

Do i actually need both of them anyway ?

Private Function ExitFunc() As Integer

If Len(Me.ComboBox9.Value) < 17 And Me.ComboBox9.Value <> "N/A" Then
    MsgBox "VIN MUST BE 17 CHARACTERS IN LENGTH" & vbCr & vbCr & "CONTINUE TO EDIT THE VIN ?", vbYesNo + vbCritical
    
If vbYes Then
   ComboBox9.SetFocus
Else
   Unload McListForm
End If
End If
ComboBox9 = UCase(ComboBox9)

End Function
Private Sub ComboBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(ComboBox9.Value) <> 17 And ComboBox9.Value <> "N/A" 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

 

[ipbr21054]

I mean im not sure why this one is even being used.

The other gives the user the option to edit value in combobox.

So is it ok to remove this one ?

Private Sub ComboBox9_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Len(ComboBox9.Value) <> 17 And ComboBox9.Value <> "N/A" 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

 

[Jeffrey Mahoney]

It all depends if you want to check that they have the right value just as they leave the combobox or when they are closing the form.

When are you calling this: Private Function ExitFunc() As Integer

 

[ipbr21054]

All done thanks.
I kept the code that checks when the user leaves the Combobox Thanks