using a hyphen on Userform.

[rlink_23]

In my Userform I have this code that when you put the customer number in and it populates the textBoxes with the customer info. I have a few customers that have a dash at the end such as 45610-2. It will not let me add a hyphen (dash). Is there a way I can use the dash on the userfom? Thank you kindly for your help!!

Code I speak about above

Private Sub custnumber_Change()Dim I As Long, Lastrow As Long
Lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row
For I = 1 To Lastrow


If Sheets("Customers").Cells(I, "A").Value = custnumber Or _
Sheets("Customers").Cells(I, "A").Value = Val(custnumber) Then


Me.custnumber = Sheets("customers").Cells(I, "A").Value
Me.company = Sheets("Customers").Cells(I, "B").Value
Me.TextBox7 = Sheets("Customers").Cells(I, "C").Value
Me.Phone1 = Sheets("Customers").Cells(I, "E").Value


Me.ZipCode = Sheets("Customers").Cells(I, "D").Value
Me.Email = Sheets("Customers").Cells(I, "F").Value






End If
If custnumber = "" Then
Me.company = Clear
Me.TextBox7 = Clear
Me.Phone1 = Clear
Me.ZipCode = Clear
Me.Email = Clear


End If
Next




End Sub

 

[Norie]

Is that all the code?

There's nothing in it that would prevent a hyphen being entered.

 

[Rick Rothstein]

In my Userform I have this code that when you put the customer number in and it populates the textBoxes with the customer info. I have a few customers that have a dash at the end such as 45610-2. It will not let me add a hyphen (dash). Is there a way I can use the dash on the userfom? Thank you kindly for your help!!

Code I speak about above

Private Sub custnumber_Change()Dim I As Long, Lastrow As Long
Lastrow = Sheets("Customers").Range("A" & Rows.Count).End(xlUp).Row
For I = 1 To Lastrow

If Sheets("Customers").Cells(I, "A").Value = custnumber Or _
Sheets("Customers").Cells(I, "A").Value = [B][COLOR="#FF0000"][SIZE=3]Val([/SIZE][/COLOR][/B]custnumber[B][COLOR="#FF0000"][SIZE=3])[/SIZE][/COLOR][/B] Then

Me.custnumber = Sheets("customers").Cells(I, "A").Value
Me.company = Sheets("Customers").Cells(I, "B").Value
Me.TextBox7 = Sheets("Customers").Cells(I, "C").Value
Me.Phone1 = Sheets("Customers").Cells(I, "E").Value

Me.ZipCode = Sheets("Customers").Cells(I, "D").Value
Me.Email = Sheets("Customers").Cells(I, "F").Value

End If
If custnumber = "" Then
Me.company = Clear
Me.TextBox7 = Clear
Me.Phone1 = Clear
Me.ZipCode = Clear
Me.Email = Clear

End If
Next

End Sub

Without setting up a test, I am pretty sure your problem stems from the use of the Val function (highlighted in red above)... it returns the number appearing before the first non-floating point character which, in this case, is the minus sign.

 

[rlink_23]

Yes thats all of the code. U type in 45610 and it will not let me add a hyphen.

 

[rlink_23]

Rick can I just remove the val function?

 

[rlink_23]

That worked Rick. Removed the Val function and it works fine now!!

 

[rlink_23]

except now the regular customer number won't work. Only with Hyphens. how do I proceed?