Correct my invoice number validation code

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
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:

Code:
[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
 
Last edited:

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
My first thought, without testing...

Rich (BB code):
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

Board Regular
Joined
Aug 22, 2010
Messages
211
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

Board Regular
Joined
Aug 22, 2010
Messages
211
Hi Cstimart

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

Kenny
 

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211

ADVERTISEMENT

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:

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:

Code:
[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
 
Last edited:

cstimart

Well-known Member
Joined
Feb 25, 2010
Messages
1,180
How about something like...


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

kenny9002

Board Regular
Joined
Aug 22, 2010
Messages
211
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,608
Messages
5,597,143
Members
414,129
Latest member
mr vba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top