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]

Just offhand:

o 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

It's not clear what you mean by "shoots me to code". What code? When does the "shooting" happen?

As it stands:

(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.

Sure there's a way ... it's hard to say more at this point.

(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)

This is possible but the conversion is not simply adding 978 - we also need to calculate (and substitute) a checksum for the original 10th digit. The formula is known but I haven't seen a vb version of it (I haven't looked very hard, though - a php version is here and between that and some documentation on isbn 13's we should be able to scrum it together).


Do your textbox values have the leading zero's or not?

ξ

 

[e211898]

I had no idea isbn10s were pretty much the same as isbn13s.I did some research and found a code that is claimed to convert from 10 to 13 in vba. Ill give it a try and post back.
here is the code im going to try for anyone else trying to do the same thing.

isbn10="978" & left(replace(Fields("ISBN10"),"-",""),9)
isbn10_1=left(isbn10,1)
isbn10_2=mid(isbn10,2,1)*3
isbn10_3=mid(isbn10,3,1)
isbn10_4=mid(isbn10,4,1)*3
isbn10_5=mid(isbn10,5,1)
isbn10_6=mid(isbn10,6,1)*3
isbn10_7=mid(isbn10,7,1)
isbn10_8=mid(isbn10,8,1)*3
isbn10_9=mid(isbn10,9,1)
isbn10_10=mid(isbn10,10,1)*3
isbn10_11=mid(isbn10,11,1)
isbn10_12=right(isbn10,1)*3
checkdigit=10-right(FormatNumber((isbn10_1+isbn10_2+isbn10_3+isbn10_4+isbn10_5+isbn10_6+isbn10_7+isbn10_8+isbn10_9+isbn10_10+isbn10_11+isbn10_12)/10,1),1)
if checkdigit=10 then checkdigit="0"
Fields("ISBN13")=isbn10 & checkdigit

 

[e211898]

some isbns have leading 0s but not all. sorry for the slang that prob didnt make since to anyone but me..."shoots me to code"....with the current code, if i enter an isbn10 and click the command button, an error message pops up (run-time,syntax,...). After the messagebox it opens the debugger and shows the vba code with highlighting.

 

[xenou]

Okey dokey.

 

[e211898]

in the code i posted above vba does not recognize "Fields(" and also i cant find it when i press F2. is there something i could use in its place for vba code.

 

[xenou]

in the code i posted above vba does not recognize "Fields(" and also i cant find it when i press F2. is there something i could use in its place for vba code.

How are you implementing the function? Fields of course must change - it must have been written for Access vba. But it depends how you want to invoke the function. You're mentioning textboxes, cells, and scanning numbers -- I'm not sure what you're doing to get the data into Excel and at what point you'd want to insert this conversion.

 

[e211898]

In my user form i have 10 textboxes. each of them is for a different isbn. when the customer comes up with a handful of books, I scan one book at the time. each scaned book is then entered via the barcode scanner into the selected textbox.......click the first textbox, then scan the book. the scanner reads the number, types it into the textbox, and automatticly hits enter which tabs to the next textbox in the list of 10 textboxes....scan the next book......repeat the process for however many books there are. when i click the total button i want the isbn10s (how ever many there are and in what ever textboxes they are in) to convert to isbn13s and then be entered into the excel spreed sheet.

i messed with the formula a little and this is what i have so far....

Private Sub ib_total_Click()
Dim tb As Integer
Dim rng As Range
Dim ls As Range
Dim cr As Range
Dim isbn As String
    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
 
 For tb = 1 To 10
    If Len(TextBox & tb) < 11 Then
 
        isbn = "978" & (TextBox & tb)
       
        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)
        h = Right(isbn, 1) * 3
 
        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
 
 
 
    '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
    
    '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 the focus to the next scan table
    Application.Goto rng.Offset(12, 0)
    
    ib_total_form.Show
End Sub

 

[e211898]

type mismatch '13'.......debugger highlights line

r = Mid(isbn, 6, 1) * 3

 

[xenou]

What's the isbn on this one? I'm a little fuzzy. Don't isbn's have all numbers except a final x? Do your scan's include hyphens? I'm assuming the function is good of course - but this sounds like bad news for confirming this is the case.