Automatically enter a character into text box

[ipbr21054]

Hi,
I forever having to type - into a text box.
Is there some code workaround to do this for me.
Example.
I would type 12345-ABC-678
With the code i would just type 12345ABC678
The - is always in the same place,so position 6 & 10
Below is the code for the text box i type it into.

Private Sub MyPartNumber_AfterUpdate()
If MyPartNumber.Text = "" Then Exit Sub
'Check to see if value exists
If WorksheetFunction.CountIf(Sheet8.Range("Y:Y"), Me.MyPartNumber.Value) = 0 Then
MsgBox "NON STOCK ITEM TRY LINGS"
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)
End With
End Sub

 

[Trevor_S]

Immediately after the Check to see if value exists comment, add the following:

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)

Checking the length first means that if you do type the -, it won't add more.

 

[ipbr21054]

Spot on.
Thanks Trev

Maybe you could write something along the same lines.
I will type it in the next reply.

 

[ipbr21054]

The form works like this.
I would put data into text box called MyPartNumber.
This data will always be like 12345-ABC-678 13 characters which includes the 2 x -
If there is a match from my database then the corresponding data is then put into my other text boxes.
If there is not match then i see a pop up message.

This is where i would like you to advise if possible.
If i enter say 12345-ABC-67 only 12 characters including the 2 x - then there wouldnt be a match and my message would pop up.
For example i wouldnt of seen that i omitted to enter the last character being 8
Basically check to make sure 13 characters have been entered so i then dont get a flase pop up message.
Remembering that if i only enter 12345ABC678 without the 2 x - because your other code will put them in for me.

Thanks if you can make this possible.

Private Sub cmdCheckButton_Click()
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)
HondaPartNumber.SetFocus
MyPartNumber.Value = UCase(MyPartNumber.Value)
Me.Notes.BackColor = RGB(255, 255, 0) ' yellow
End Sub

 

[Trevor_S]

Try amending your original code as follows (I've also added in the bit about the -s).

If the part number is not 13 characters (including the -s), they background will turn red, and the pop up message will not appear. The first line of the code resets the text box to a white background (amend this if your text boxes start as a different colour), in case the previous entry in the box turned it red.

Private Sub MyPartNumber_AfterUpdate()
'Set the background to white (in case its currently red due to an incorrect length part number having been entered)
Me.MyPartNumber.BackColor = RGB(255, 255, 255)
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
MsgBox "NON STOCK ITEM TRY LINGS"
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)
End With
End Sub

 

[ipbr21054]

Morning,
What can i say,,,!00% happy & works a treat.
Just a question.
When i first run the form the | is flashing in the text box waiting for me to enter the data.
If i enter the wrong data then press the check button then the cell turns red as it should but the | is now flashing in the text box called HOndaPartNumber.
Is there a workaround so the| only flashes in the box im to enter the dat & not to continue into the next text box if the entered dat is incorrect ?
Having said that ive also noticed that it starts flashing even if the code is correct.

Many thanks for a great piece of code.

 

[Trevor_S]

A couple of suggestions...

1. You have two lines that read "Me.MyPartNumber.SetFocus". Try changing these to "Me.MyPartNumber.Activate". I've never used SetFocus before, perhaps its not enough to set the position of the flashing |? I'm a bit more certain that Activate would move it!

2. But if that doesn't solve it, bear in mind that the code runs after the MyPartNumber has been updated. I think this "event" technically occurs just before the Check button is clicked. So its possible that the "MyPartNumber_AfterUpdate()" coding is working fine, but the "CheckButton_Click()" (or whatever its called) coding then immediately puts the flashing | elsewhere. Try putting either "Me.MyPartNumber.SetFocus" or "Me.MyPartNumber.Activate" just before the end (or "ends" if there is more than one possible outcome) of the coding for "CheckButton_Click()". Then whatever "CheckButton_Click()" does, it should return the flashing | to the right place once its finished.

 

[ipbr21054]

Hi,
Thanks.
Ive tried numerous combinations but none worked.
I will just leave it and put up with it.

Many thanks for advising anyway.