Are Values From A String A Number

[Ark68]

I have this (exit event) code that error checks the value entered into a textbox (tb_pn) on my userform. This textbox is in focus when the userform opens.
On my userform, I also have an "Exit" button. This exit button simply closes the userform.

But when I click the Exit Button, the, exit event code for tb_pn triggers. Is there a better event to use for triggering the error checking code? What can I do to avoid this behaviour?

Private Sub tb_pn_exit(ByVal Cancel As MSForms.ReturnBoolean) 'beforeupdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not mbevents Then Exit Sub
    If Len(tb_pn) > 7 Or Len(tb_pn) < 5 Then
        MsgBox "Please enter a valid permit number {R####..}.", vbExclamation, "PERMIT NUMBER ERROR"
    ElseIf Right(tb_pn, 1) <> "R" Then
        MsgBox "Please enter a valid permit number {R####..}.", vbExclamation, "PERMIT NUMBER ERROR"
    ElseIf Len(tb_pn) - 1 < 4 Or Len(tb_pn) - 1 > 6 Then
        MsgBox "Please enter a valid permit number {R####..}.", vbExclamation, "PERMIT NUMBER ERROR"
    nom = Left(tp_pn, lentp_pn - 1)
    ElseIf IsError(CDbl(nom)) Then
        MsgBox "Please enter a valid permit number {R####..}.", vbExclamation, "PERMIT NUMBER ERROR"
    Else
        Me.tb_pn.BackColor = vbWhite
        Me.cbx_rcode.BackColor = clr_blue
    End If
End Sub

 

[NdNoviceHlp]

Maybe the Textbox exit event would be better? Dave

 

[Ark68]

Hi NdNoviceHlp ... thanks for stepping in with your suggestion. I believe I am using a textbox exit event already.

 

[Rick Rothstein]

But when I click the Exit Button, the, exit event code for tb_pn triggers. Is there a better event to use for triggering the error checking code? What can I do to avoid this behaviour?

I am a little confused at what you are asking here. Are you saying you don't want to check what is in tb_pn before closing down the UserForm?

Also, as a side note, you have this for your first ElseIf test...

ElseIf Right(tb_pn, 1) <> "R" Then

but you show the R as being needed at the front of the permit number, not the back of it... did you mean to use the Left function call there instead of the Right function call?

Along the same lines as my side note, you also grab the number from the permit number by using this line of code...

nom = Left(tp_pn, lentp_pn - 1)

which also seems to be assuming the R is on the right... did you perhaps mean to use the Right function here (or better yet the Mid function)?

 

[Ark68]

Hi Rick ... sorry for the confusion.
The "Exit" button is an escape, to close the form basically with or without any data being entered (I have another button that submits the data to keep). An "oops, I don't need this userform afterall". So if the form is opened, the focus is on tb_pn, the user clicks exit without entering any data because they don't want to be here, and it triggers the event exit code advising that the textbox is missing information. Hitting the exit button a second time actually exits and closes the userform with the code assigned to that button.

The other stuff is all error checking. The value that the user enters must start with an "R" and have between 4 and 6 trailing numbers. So whether it's the best logic or not ... I first check to make sure their entry starts with an "R", then I check to see if it's an appropriate length (if it got that far we know that the first of 4-6 characters is in place), and then finally I am trying to find a way to ensure that the trailing numbers (4-6 digits) are numeric, not containing letters or special characters.

I did correct a typo:

nom = Left(tp_pn, lentp_pn - 1)

should be

nom = Left(tb_pn, lentp_pn - 1)

 

[Ark68]

And yes ... I had my lefts and rights mixed up.

 

[Rick Rothstein]

Do you have any other event procedures set up for the tb_pn TextBox besides the exit procedure you showed us? If so, can you post them.

 

[Ark68]

Hi Rick,
This is the only event procedure set up for this textbox control (tb_pn).