User forms _Change()

[AlanAnderson]

Hi,
I have a little routine that is supposed to test that duplication does not occur. Problem is that if I use tbPPCode_Click the user may bypass it by using tab keys. If I use tbPPCode_Change the event triggers on the second key pressed. How can I force this routine to wait untill the client moves on to the next field

Thanks,

Alan

Private Sub tbPPCode_Change()
Dim PPCode As String
Dim FinalRowPricePoint As Long
FinalRowPricePoint = Cells(Rows.Count, 1).End(xlUp)
Sheets("PricePricePoint").Select
    For i = 2 To FinalRowPricePoint
        If Me.tbPPCode = Cells(i, 1) Then
            MsgBox "You may not enter duplicate codes"
            Me.tbPPCode = ""
        End If
    Next i
End Sub

 

[ColinKJ]

Hi Alan

Is your tbPPCode a TextBox.

Have you tried

Private Sub TextBox1_Exit

ColinKJ

 

[AlanAnderson]

Hi Colin, Thanks for reply. I just tried that and get "Compile Error: Procedure declaration does not match description of event or procedure having the same name"

tbPPCode is a text box within a user form. BTW I'm using 2003 - maybe that makes a difference.

Thanks,

Alan

 

[RoryA]

The full declaration is:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

To get this, select Textbox1 from the left dropdown at the top of the main code window, then select Exit from the right-hand dropdown.

 

[AlanAnderson]

Thank you so much "Old Father Time". It works like a dream.
I do appreciate the help I get.
Regards,

Alan

 

[ColinKJ]

Alan,

You will need to use the Exit for your TextBox tbPPCode.

You will need to go into your code, and first select your tbPPCode from the top lefthand dropdown, then, from the top righthand dropdown, select Exit.

You should get a private sub something like

Private Sub tbPPCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)

ColinKJ

 

[AlanAnderson]

Thanks Colin, I appreciate your help. This works!!

Regards,

Alan