An exception within the code

[ipbr21054]

Hi,
I have the code in use shown below.

When i enter a number into the field My Partner 99.9% of the time the character count will be 11 BUT an oddball has been recently introduced where it is a 13 characters count.
This is the ONLY 13 character number of its kind.
I DONT wish to alter the 11 to 13 in the code as this will open up a huge can of worms for people typing 12 characters etc & we then have a big clean up job,currently in place is a msgbox which works perfect.

So if possible what i require is the code to work the same at present BUT to allow the only 13 character of which is 08E56CAT88802 if so allow it but if not then show the msgbox PartNumberMessage.Show

Private Sub MyPartNumber_AfterUpdate()'Set the background to grey (in case its currently red due to an incorrect length part number having been entered)
Me.MyPartNumber.BackColor = RGB(180, 180, 180)
If MyPartNumber.Text = "" Then Exit Sub


'Add - characters if 11 part number is 11 characters long
If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Left(Me.MyPartNumber.Value, 5) & "-" & Mid(Me.MyPartNumber.Value, 6, 3) & "-" & Right(Me.MyPartNumber.Value, 3)


'Turn background red if part number is wrong length
If Len(Me.MyPartNumber.Value) <> 13 Then
Me.MyPartNumber.BackColor = RGB(255, 0, 0)
Me.MyPartNumber.SetFocus
Exit Sub
End If


'Check to see if value exists
If WorksheetFunction.CountIf(Sheet8.Range("Y:Y"), Me.MyPartNumber.Value) = 0 Then
PartNumberMessage.Show
Me.MyPartNumber.Value = ""
Me.MyPartNumber.SetFocus
Exit Sub
End If


'Lookup values based on first control
With Me
.HondaPartNumber = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 2, 0)
.NumbersOnCase = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 3, 0)
.NumbersOnPcb = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 4, 0)
.Buttons = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 5, 0)
.GoldSwitchesOnPcb = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 6, 0)
.ItemType = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 7, 0)
.Notes = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 8, 0)
.Upgrade = Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 9, 0)
.MyPrice = Format(Application.WorksheetFunction.VLookup(Me.MyPartNumber, Sheet8.Range("HONDAORIGINALNUMBERS"), 10, 0), "£#,##0.00")
End With
End Sub

Have a nice day

 

[Rick Rothstein]

When i enter a number into the field My Partner 99.9% of the time the character count will be 11 BUT an oddball has been recently introduced where it is a 13 characters count.
This is the ONLY 13 character number of its kind.

So if possible what i require is the code to work the same at present BUT to allow the only 13 character of which is 08E56CAT88802 if so allow it but if not then show the msgbox PartNumberMessage.Show

'Add - characters if 11 part number is 11 characters long
If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Left(Me.MyPartNumber.Value, 5) & "-" & Mid(Me.MyPartNumber.Value, 6, 3) & "-" & Right(Me.MyPartNumber.Value, 3)

Where do the dashes go for the 13-character part number?

 

[ipbr21054]

Hi,
Here we go.

08E56-CAT-88802

 

[Rick Rothstein]

Hi,
Here we go.

08E56-CAT-88802

This is untested, but I believe this line of code should work if you use it in place of the line of code I quoted in Message #2 ...

[table="width: 500"]
[tr]
	[td]If Len(Me.MyPartNumber.Value) = 11 Or Me.MyPartNumber = "08E56CAT88802" Then Me.MyPartNumber.Value = Format(Me.MyPartNumber.Value, "@@@@@-@@@-@@@")[/td]
[/tr]
[/table]

I am not sure what the section of code starting with the comment "'Turn background red if part number is wrong length" is for, but I am not sure if you would need it any more or not.

 

[ipbr21054]

Hi,
Rick did you post 2 items as i received 2 emails but i only see 1 post ?

I removed this.

If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Left(Me.MyPartNumber.Value, 5) & "-" & Mid(Me.MyPartNumber.Value, 6, 3) & "-" & Right(Me.MyPartNumber.Value, 3)

And replaced it with this,

[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]If Len(Me.MyPartNumber.Value) = 11 Or Me.MyPartNumber = "08E56CAT88802" Then Me.MyPartNumber.Value = Format(Me.MyPartNumber.Value, "@@@@@-@@@-@@@")

But it did not work and the background is now red.
Red means that there entered part number isnt 11 characters[/TD]
[/TR]
</tbody>[/TABLE]

 

[ipbr21054]

Sorry i forgot to mention that when i typed in the 13 characters the cell changed red.

 

[ipbr21054]

Hi,
Ive also found another piece of code higher up the sheet.

Private Sub cmdCheckButton_Click()If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Format(Me.MyPartNumber, "@@@@@-@@@-@@@")
MyPartNumber.Value = UCase(MyPartNumber.Value)
HondaPartNumber.SetFocus
End Sub

 

[Rick Rothstein]

Hi,
Ive also found another piece of code higher up the sheet.

Private Sub cmdCheckButton_Click()If Len(Me.MyPartNumber.Value) = 11 Then Me.MyPartNumber.Value = Format(Me.MyPartNumber, "@@@@@-@@@-@@@")
MyPartNumber.Value = UCase(MyPartNumber.Value)
HondaPartNumber.SetFocus
End Sub

Can you post all of the code you have which does anything with the MyPartNumber control and please describe in what way the code line I posted earlier did not work for you.

 

[ipbr21054]

OK i will do that.

Before i asked here if you typed anything but 11 characters then that cell would turn red.
With your code it turned red.

Shall i share my workbook for you,then you can see it for real etc.

 

[ipbr21054]

Rick,here we go

https://drive.google.com/open?id=1ELhW4pwWrVi3tsduJOn4XwtWghMLNJQU

HONDA SHEET
Enter number into field where cursor is flashing then press CHECK