Correct my invoice number validation code

[kenny9002]

Hi All,

I wish to make entries into a database by using a Userform. The first textbox is named 'txtInv' and this is for entering the Invoice No. The format of the invoice no. is alphanumeric - two alphabets followed by either 3 or 4 numbers, e.g. 'PQ###' or 'PQ####'.

I also wish to make it possible for users to enter the preceding two alphabets either in small letters.e.g. 'pq' or in capital letters, e.g. 'PQ'. Anything else should bring up an error message box.

I came up with the following code which I gleaned from a previous code, but I am not sure I got it right as it is coming up with error message:

[FONT=Arial]Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>[/FONT]
[FONT=Arial]If txtInv.Value = vbNullString Then Exit Sub<o:p></o:p>[/FONT]
[FONT=Arial]If (Not UCase(Me.txtInv.Value)Like "PQ###")And<o:p></o:p>[/FONT]
[FONT=Arial]If (Not UCase(Me.txtInv.Value) Like "PQ####") Then<o:p></o:p>[/FONT]
[FONT=Arial]MsgBox "Non Valid Invoice Number"<o:p></o:p>[/FONT]
[FONT=Arial]Cancel = True<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]
[FONT=Arial]End Sub<o:p></o:p>[/FONT]

I should be grateful for a correction to the code to make it work for me. I must confess that I am very new to VBA. Therefore, if the code does not make much sense and an alternative would be appropriate, I would be glad for help with it.

Thanks for any anticipated help.

Kenny

 

[cstimart]

My first thought, without testing...

Private Sub txtInv_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If txtInv.Value = vbNullString Then Exit Sub
If (Not UCase(Me.txtInv.Value) Like "PQ###") And (Not UCase(Me.txtInv.Value) Like "PQ####") Then
    MsgBox "Non Valid Invoice Number"
    Cancel = True
End If
End Sub

 

[kenny9002]

Hi Cstimart,

Thanks for your prompt and kind response.

I tried your version of the code earlier but I kept having an error message. I've tried it again and still have the same error message.

Please could you kindly look into it again to see if it requires a bit more tweaking.

Thanks very much.

kenny

 

[kenny9002]

Hi Cstimart

Again, thanks for your help. I tried it again and it worked OK. I am grateful.

Kenny

 

[kenny9002]

Please can I get some help with the following userform validation code which is not working properly for me.

I have a userbox with a couple of textboxes - the last being the 'txtPD' textbox. I want users to enter only a particular text into the textbox . And the text is the word 'Unpaid' either as 'unpaid' (i.e. in lowercase letters) or 'Unpaid' (i.e. in sentence case), or 'UNPAID' (i.e. in Uppercase).


To try to do that, I have the following code:

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Arial]If Not (Me.txtDP.Value) = "unpaid"  Then<o:p></o:p>[/FONT]
[FONT=Arial]MsgBox "Please Enter the Word  'Unpaid' in the Box."<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtDP.SetFocus<o:p></o:p>[/FONT]
[FONT=Arial]Exit Sub<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]

The above works, but only just for one of the options - i.e. the 'unpaid' (lowercase) option. I am not able to get the code to handle the three options of either 'unpaid', 'Unpaid' or 'UNPAID'.

I did a few searches and tried the following:

[FONT=Arial][COLOR=red]If Not (Me.txtDP.Value) = "unpaid" Or (Not (Me.txtDP.Value) = "Unpaid" Or (Not (Me.txtDP.Value) = "UNPAID")) Then<o:p></o:p>[/COLOR][/FONT]
[FONT=Arial]MsgBox "Please Enter the Word  'Unpaid' in the Box."<o:p></o:p>[/FONT]
[FONT=Arial]Me.txtDP.SetFocus<o:p></o:p>[/FONT]
[FONT=Arial]Cancel = True<o:p></o:p>[/FONT]
[FONT=Arial]Exit Sub<o:p></o:p>[/FONT]
[FONT=Arial]End If<o:p></o:p>[/FONT]

I can't get it to work. Please could I get help with this.

Thanks very much.

Kenny

 

[cstimart]

How about something like...


If Ucase(Me.txtDP.Value) <> "UNPAID Then

 

[kenny9002]

Thank you very much Cstimart. You've again made my day! Your suggestion is the solution I was looking for.

Very grateful to you.

Best wishes.

Kenny