Code is not entering content into activecell.

[e211898]

The code I am using is not entering the number typed in the textbox into the active cell. I am using this program to scan ISBN numbers and have them put into an array on the spreadsheet. the spreadsheet then looks up an associated price and returns the value. The number must be in number format when entered into the cell. Right now the command button is causing "" to be entered into the activecell.

Private Sub ib_total_Click()
 
Dim TextBox3 As Single
Dim TextBox4 As Single
Dim TextBox5 As Single
Dim TextBox6 As Single
Dim TextBox7 As Single
Dim TextBox8 As Single
Dim TextBox9 As Single
Dim TextBox10 As Single
Dim TextBox11 As Single
Dim TextBox12 As Single
 
 
Worksheets("Scan Search").Activate
Range("B15").Activate
 
'find the first blank scan table
If Not IsEmpty(ActiveCell) Then
Do Until ActiveCell = ""
ActiveCell.Offset(12, 0).Activate
Loop
End If
 
'enter first ISBN into form
'and check that a number is scaned
If Not isnumeric(TextBox3) Then
MsgBox "Please scan the order.", vbOKOnly, "No Scan"
Exit Sub
Else
ActiveCell = TextBox3
End If
 
'enter the rest of the scans into the form
If Not isnumeric(TextBox4) Then
ActiveCell.Offset(1, 0) = ""
Else
ActiveCell.Offset(1, 0) = Fix(TextBox4)
End If
If Not isnumeric(TextBox5) Then
ActiveCell.Offset(2, 0) = ""
Else
ActiveCell.Offset(2, 0) = Fix(TextBox5)
End If
If Not isnumeric(TextBox6) Then
ActiveCell.Offset(3, 0) = ""
Else
ActiveCell.Offset(3, 0) = Fix(TextBox6)
End If
If Not isnumeric(TextBox7) Then
ActiveCell.Offset(4, 0) = ""
Else
ActiveCell.Offset(4, 0) = Fix(TextBox7)
End If
If Not isnumeric(TextBox8) Then
ActiveCell.Offset(5, 0) = ""
Else
ActiveCell.Offset(5, 0) = Fix(TextBox8)
End If
If Not isnumeric(TextBox9) Then
ActiveCell.Offset(6, 0) = ""
Else
ActiveCell.Offset(6, 0) = Fix(TextBox9)
End If
If Not isnumeric(TextBox10) Then
ActiveCell.Offset(7, 0) = ""
Else
ActiveCell.Offset(7, 0) = Fix(TextBox10)
End If
If Not isnumeric(TextBox11) Then
ActiveCell.Offset(8, 0) = ""
Else
ActiveCell.Offset(8, 0) = Fix(TextBox11)
End If
If Not isnumeric(TextBox12) Then
ActiveCell.Offset(9, 0) = ""
Else
ActiveCell.Offset(9, 0) = Fix(TextBox12)
End If
'set the focus to the next scan table
ActiveCell.Offset(12, 0).Activate
 
 
End Sub

 

[Norie]

Don't use ActiveCell.

The code might actually be putting something in what it considers is the active cell on what it considers the active sheet.

Try setting a reference to the cell you want to start at, which I assume is B15 on worksheet 'Scan Sheet'.

Option Explicit
 
Private Sub ib_total_Click()
Dim rng As Range
Dim TextBox3 As Single
Dim TextBox4 As Single
Dim TextBox5 As Single
Dim TextBox6 As Single
Dim TextBox7 As Single
Dim TextBox8 As Single
Dim TextBox9 As Single
Dim TextBox10 As Single
Dim TextBox11 As Single
Dim TextBox12 As Single
 
    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
 
    'enter first ISBN into form
    'and check that a number is scaned
    If Not IsNumeric(TextBox3) Then
        MsgBox "Please scan the order.", vbOKOnly, "No Scan"
        Exit Sub
    Else
        rng = TextBox3
    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) = Fix(TextBox4)
    End If
    
    If Not IsNumeric(TextBox5) Then
        rng.Offset(2, 0) = ""
    Else
        rng.Offset(2, 0) = Fix(TextBox5)
    End If

    If Not IsNumeric(TextBox6) Then
        rng.Offset(3, 0) = ""
    Else
        rng.Offset(3, 0) = Fix(TextBox6)
    End If

    If Not IsNumeric(TextBox7) Then
        rng.Offset(4, 0) = ""
    Else
        rng.Offset(4, 0) = Fix(TextBox7)
    End If

    If Not IsNumeric(TextBox8) Then
        rng.Offset(5, 0) = ""
    Else
        rng.Offset(5, 0) = Fix(TextBox8)
    End If

    If Not IsNumeric(TextBox9) Then
        rng.Offset(6, 0) = ""
    Else
        rng.Offset(6, 0) = Fix(TextBox9)
    End If

    If Not IsNumeric(TextBox10) Then
        rng.Offset(7, 0) = ""
    Else
        rng.Offset(7, 0) = Fix(TextBox10)
    End If

    If Not IsNumeric(TextBox11) Then
        rng.Offset(8, 0) = ""
    Else
        rng.Offset(8, 0) = Fix(TextBox11)
    End If

    If Not IsNumeric(TextBox12) Then
        rng.Offset(9, 0) = ""
    Else
        rng.Offset(9, 0) = Fix(TextBox12)
    End If
    
    'set the focus to the next scan table
    Application.Goto rng.Offset(12, 0)
End Sub

By the way what are Textbox3-12?

If they are actual textboxes controls you shouldn't need to declare them.

In fact declaring them, or variables with the same names, might cause problems.

 

[e211898]

after trying that suggestion:

Im getting a run-time error '91'

debugging highlights the line rng = worksheets("Scan Search").range("B15")

BTW the original code was activating the correct cell, only it was entering ""......0 value, in otherwords. and to answer your question, textbox3-12 refer to the names of the textboxes where the ISBNs are entered by the user

 

[Norie]

What error message to do you get?

By the way I think I know why you are getting 0 values, it's because you've declared the variables Textbox3-12.

You already have the textbox objects Textbox3-12, so now you also have variables with the same name.

The solution might be as simple as removing the declarations.

That should work if you are using a userform, if they are controls on a worksheet that could make a difference.

 

[e211898]

After some tinkering I came up with this but I was still getting a runtime error '91' with the blue line highlighted. then i deleted the last dim statement and got msg telling me that only comments can be placed after end sub. it highlighted the line in red

Private Sub ib_clear_scans_Click()
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
 
End Sub
Private Sub ib_ct_clockin_Click()
    ib_clock_in.Show
End Sub
Private Sub ib_ct_clockout_Click()
    ib_clockout.Show
End Sub
Private Sub ib_ct_endbuyback_Click()
    ib_confirm_close.Show
End Sub
Private Sub ib_price_check_Click()
    If Not isnumeric(TextBox13) Then
        MsgBox "To chenk a price, please scan an ISBN.", vbOKOnly, "Error"
        cancel = True
        Exit Sub
    End If
 
    Worksheets("Price List").Range("J6") = Val(TextBox13)
    Worksheets("Price List").Activate
    Range("J9").Activate
 
    If Not isnumeric(activecell) Then
        ib_total_label.Caption = Worksheets("Price List").Range("K9")
    Else
        ib_total_label.Caption = Worksheets("Price List").Range("J9")
    End If
End Sub
Private Sub ib_scan_more_Click()
 
    If Not isnumeric(TextBox12) Then
        MsgBox "Please scan 10 books before using this form.", vbOKOnly
        Else
        ib_more_books.Show
    End If
End Sub
Private Sub ib_total_Click()
 
    Dim activecell as Single
 
    Worksheets("Scan Search").Activate
    Range("B15").Activate
 
    'find the first blank scan table
    If Not IsEmpty(activecell) Then
        Do Until activecell = ""
            activecell.Offset(12, 0).Activate
        Loop
    End If
 
        'enter first ISBN into form
        'and check that a number is scaned
    If Not isnumeric(TextBox3) Then
        MsgBox "Please scan the order.", vbOKOnly, "No Scan"
        Exit Sub
    Else
        activecell = CSng(TextBox3)
    End If
 
    'enter the rest of the scans into the form
        If Not isnumeric(TextBox4) Then
            activecell.Offset(1, 0) = ""
        Else
            activecell.Offset(1, 0) = Fix(TextBox4)
        End If
        If Not isnumeric(TextBox5) Then
            activecell.Offset(2, 0) = ""
        Else
            activecell.Offset(2, 0) = Fix(TextBox5)
        End If
        If Not isnumeric(TextBox6) Then
            activecell.Offset(3, 0) = ""
        Else
            activecell.Offset(3, 0) = Fix(TextBox6)
        End If
        If Not isnumeric(TextBox7) Then
            activecell.Offset(4, 0) = ""
        Else
            activecell.Offset(4, 0) = Fix(TextBox7)
        End If
        If Not isnumeric(TextBox8) Then
            activecell.Offset(5, 0) = ""
        Else
            activecell.Offset(5, 0) = Fix(TextBox8)
        End If
        If Not isnumeric(TextBox9) Then
            activecell.Offset(6, 0) = ""
        Else
            activecell.Offset(6, 0) = Fix(TextBox9)
        End If
        If Not isnumeric(TextBox10) Then
            activecell.Offset(7, 0) = ""
        Else
            activecell.Offset(7, 0) = Fix(TextBox10)
        End If
        If Not isnumeric(TextBox11) Then
            activecell.Offset(8, 0) = ""
        Else
            activecell.Offset(8, 0) = Fix(TextBox11)
        End If
        If Not isnumeric(TextBox12) Then
            activecell.Offset(9, 0) = ""
        Else
            activecell.Offset(9, 0) = Fix(TextBox12)
        End If
        'set the focus to the next scan table
        activecell.Offset(12, 0).Activate
 
        ib_total_form.Show
 
 
 
End Sub
Private Sub ib_undo_Click()
    ib_scan_form.UndoAction
End Sub
Private Sub ib_close_form_Click()
    ib_manual_password.Show
End Sub
 
Private Sub UserForm_QueryClose _
  (cancel As Integer, CloseMode As Integer)
'   Prevents use of the Close button
    If CloseMode = vbFormControlMenu Then
        MsgBox "Use the control buttons to navigate the forms."
        cancel = True
    End If
End Sub

 

[Norie]

I strongly recommend you don't declare a variable call activecell.

ActiveCell (notice the capitals by the way) is a VBA property.

Did you try what I suggested? ie using a object variable like rng

Another thing you shouldn't need and probably should avoid is using Activate.

 

[e211898]

I quit fooling around with my tinkering and copied your suggestion directly intomy code. it is entering "0" with no error messages in all 10 textboxes.

 

[Norie]

Did you remove the declarations of Textbox3-12 in the code?

I did mention it but didn't remove them because I wasn't sure what they were.

Also, and perhaps it's just a typo, there is nothing in the code that is populating the textboxes apart from the ib_clear_scans button's click event.

And that isn't putting 0 in it's putting "" in them.

 

[e211898]

I hope im understanding you correctly with the ib_clear_scans button, but when the clear scans button is clicked it clears the scans in order to facilitate the ease of re entry by the user. when the form is opened it is expected that the user will populate these textboxes manually by scaning a barcode with a scaner i have. once the user populates these fields, the total button is clicked and thats where the problems start. once the total button is clicked it is expected that the code will enter any given numbers into my spreadsheet, and wherever there is a blank textbox for the code to enter "" into the given cell on the spreadsheet......I just removed the declirations for textboxes 3-12 per your recommendation and im getting the same result. 0 is being entered into the form where the numbers should be regardless of how many textboxes are filled.

 

[e211898]

Wait i just saved and restarted the excel program and now it seems to be working! im going to keep trying it with different scens and try to break the code through the user form but i think that might be it.....u just saved the rest of my hair from being pulled out!