make sure textbox has specific text in it?

[e211898]

uerform1 with textbox1 in it is for scanning ISBN #s.
there are 2 types of ISBNs, a 10 digit and a 13 digit. the 10 digit sometimes contains letters and sometimes has a 0 as the first digit in the number. my cells and my macro are designed to enter values as numeric. so if applicable, the 0 gets cut off and/or the letters try to enter into the cell and excel gives me an error and shoots me to code. this happins because the numbers in my database are entered correctly as 13 digit ISBNs.

I need either:
(1)
A code that allows the 0 to stay in the number when entered into the cell and still be formatted as a number. A code that allows letters to be entered into the cell and still be formatted as a number. OR a way to list my database to include both the 10 digit and 13 digit ISBNs and still be able to access them using a worksheet formula that includes VLOOKUP, LOOKUP, or something like that.

OR
(2)
code that makes sure the number scanned into the textbox is 13 digits AND the first 3 digits are"978"(for some reason all 13 digit ISBNs start with 978, kinda seems like it defeats the purpose but whatever)

Im not sure how much it will help but the code i have so far is below.

Private Sub ib_total_Click()
Dim i As Integer
Dim rng As Range
Dim ls As Range
Dim cr As Range
 
'locate the cumulative price on the scan sheet
Worksheets("Scan Search").Activate
Range("G1048576").End(xlUp).Activate
 
Set ls = activecell
 
'make sure 13 digit ISBN has been entered and was looked up and priced
If IsError(ls) Then
MsgBox "Scan Error." & vbCrLf & _
"Please check the scan and try again." & vbCrLf & _
"If the problem continues, please contact CASHFORBOOKS.", vbOKOnly, "Scan Error"
 
'if error then find it
Worksheets("Scan Search").Range("E1048576").End(xlUp).Activate
 
If Not isnumeric(activecell) Then
Do Until isnumeric(activecell)
activecell.Offset(-12, 0).Activate
Loop
End If
 
activecell.Offset(2, -3).Activate
 
Set cr = activecell
 
'clear scans from scan table
cr = ""
cr.Offset(1, 0) = ""
cr.Offset(2, 0) = ""
cr.Offset(3, 0) = ""
cr.Offset(4, 0) = ""
cr.Offset(5, 0) = ""
cr.Offset(6, 0) = ""
cr.Offset(7, 0) = ""
cr.Offset(8, 0) = ""
cr.Offset(9, 0) = ""
 
Unload Me
Unload ib_more_books
Exit Sub
End If
 
Set rng = Worksheets("Scan Search").Range("B15")
 
'find the first blank scan table
If Not IsEmpty(rng) Then
Do Until rng = ""
Set rng = rng.Offset(12, 0)
Loop
End If
 
'check that a number is scaned
'and enter first ISBN into form
If Not isnumeric(TextBox3) Then
MsgBox "Please scan the order.", vbOKOnly, "No Scan"
Exit Sub
Else
rng = TextBox3.Value
End If
 
'enter the rest of the scans into the form
If Not isnumeric(TextBox4) Then
rng.Offset(1, 0) = ""
Else
rng.Offset(1, 0) = TextBox4.Value
End If
If Not isnumeric(TextBox5) Then
rng.Offset(2, 0) = ""
Else
rng.Offset(2, 0) = TextBox5.Value
End If
If Not isnumeric(TextBox6) Then
rng.Offset(3, 0) = ""
Else
rng.Offset(3, 0) = TextBox6.Value
End If
If Not isnumeric(TextBox7) Then
rng.Offset(4, 0) = ""
Else
rng.Offset(4, 0) = TextBox7.Value
End If
If Not isnumeric(TextBox8) Then
rng.Offset(5, 0) = ""
Else
rng.Offset(5, 0) = TextBox8.Value
End If
If Not isnumeric(TextBox9) Then
rng.Offset(6, 0) = ""
Else
rng.Offset(6, 0) = TextBox9.Value
End If
If Not isnumeric(TextBox10) Then
rng.Offset(7, 0) = ""
Else
rng.Offset(7, 0) = TextBox10.Value
End If
If Not isnumeric(TextBox11) Then
rng.Offset(8, 0) = ""
Else
rng.Offset(8, 0) = TextBox11.Value
End If
If Not isnumeric(TextBox12) Then
rng.Offset(9, 0) = ""
Else
rng.Offset(9, 0) = TextBox12.Value
End If
 
activecell.Offset(10, 4) = 1
 
'set the focus to the next scan table
Application.Goto rng.Offset(12, 0)
 
ib_total_form.Show
End Sub

 

[xenou]

Does the function expect you to include the 978 in the isbn checksum algorithm? Where did this function come from?

Also h variable in the function looks odd - as if its including the checksum digit in the checksum calculation. I think the function should "get" the first 12 digits to work on, probably (assuming the prefix is indeed part of the algorithm).

 For tb = 1 To 10
    If Len(TextBox & tb) < 11 Then
 
        [COLOR="Red"]isbn = "978" & (TextBox & tb)[/COLOR]
       
        c = Left(isbn, 1)
        o = Mid(isbn, 2, 1) * 3
        n = Mid(isbn, 3, 1)
        v = Mid(isbn, 4, 1) * 3
        e = Mid(isbn, 5, 1)
        r = Mid(isbn, 6, 1) * 3
        t = Mid(isbn, 7, 1)
        i = Mid(isbn, 8, 1) * 3
        s = Mid(isbn, 9, 1)
        b = Mid(isbn, 10, 1) * 3
        g = Mid(isbn, 11, 1)
[COLOR="Red"]        h = Right(isbn, 1) * 3[/COLOR]
 
        checkdigit = 10 - Right(FormatNumber((c + o + n + v + e + r + t + i + s + b + g + h) / 10, 1), 1)
        If checkdigit = 10 Then checkdigit = "0"
        isbn13 = isbn & checkdigit
        
        tb = tb + 1
    End If
Next tb

 

[e211898]

yeah two different sites said to include the "978" in the algorithm. i forget what 2 sites i looked at. all books ive seen have dashes in the isbn10 but not necesserally an x at the end. about the formula for the checkdigit calculation, there is only the first 12 digits included in the math formula. here is a book that ive been using to play with the code.

0 4 7 1 3 5 6 4 7 6
add "978" gives you.....
c o n v e r t i s b g h (6) not included in the formula
9 7 8 0 4 7 1 3 5 6 4 7 6
x1 x3 x1 x3x1 x3x1x3 x1 x3 x1x3 drop the six gives you....
9+21+8+0+4+21+1+9+5+18+4+21=121
take the last digit..(1)..from (121) and subtract it from 10 gives you...
9
put it in place of the digit you removed gives you...
9780471356479........or it should at least

as u can tell from my formula is there a reason why im getting a type mismatch error?

 

[xenou]

This syntax looks wrong now that I take another look. If this code is in your userform I'd expect (assuming tb is a counter for looping your textboxes from 1 to 10):

isbn = "978" & Me.Controls("TextBox" & tb).Text

Also, I still don't like this line, since it refers to the last digit, which is the check digit itself:

h = Right(isbn, 1) * 3

I think you need to pass the argument the left 12 digits and not all 13:

isbn = [COLOR="Blue"]Left([/COLOR]"978" & Me.Controls("TextBox" & tb).Text[COLOR="Blue"],12)[/COLOR]