Adding a value from text box to the last used cell

  • Dear friends, hi
    I have a userform with a text box. On KeyDown this text box value has to be sent to the last used cell in Column range( E4:E14)......of the active sheet.


    Above code isn't working the way i want to.
    Help will be appreciated

  • Hello,


    From a programming standpoint, no error in your code ...


    How do you want the code to operate ...?


    Have you tried : Private Sub TextBox1_Change()


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi Carim,.Thanks for the reply
    well....This code works only when the column "E" has no data below Cell E14....Where as I have other tables below this range. I would want the numeric value i feed in textbox to populate/replace the last empty/or otherwise filled, Row in the column range(E4:E14). and this should happen on KeyDown event of textBox4.
    Also I have named this range (E4:E14) as "Quantity" for convenience.
    Please advise,
    Thanks in advance

  • Hello,


    A couple of remarks :


    1. You should NEVER have tables below tables ... !!!


    2. You can use following : Range("E4").End(xlDown) + 1


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sorry I have uploaded the whole workbook unedited.Most is self explanatory....formSaleScan scans the bar code with default qty. as 1. Textbox 4 is for manually adding the quantity which is sent to the spread sheet "Quantity column E4 To E14
    to go to the worksheet use "SetUp" , use "admin" and " as passcode "1234" and navigate to sheet
    After you complete download let me know so ill delete the worksheet

  • Thanks for the file ...


    So you need to add TextBox4 manual input ... to the First Empty cell ONLY in the E4:E14 range ...


    What needs to be done if all cells are already filled with data ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi....forgot to mention ......There are two two inputs for quantity....one is next to the barcode scanner box and one is for the "manual Entry".instead of using the Bar code scanner....you can use the "manual Entry" input method also but there again the "quantity input" box is incomplete.
    I am sorry if the workbook is too large and odd, but I am just a 4 months old in learning this subject,, so please excuse
    Regards

  • Hi,
    the last cell in range will be already fill with quantity "1" it has to be replaced by the new value


    So ... it is NOT an issue to find the first Empty cell ... !!!


    Please Clarify ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Sorry i didn't get you. But i'll explain.....when the bar code is scanned it is sent to range "C2" and accordingle C4 gets pop[ulated with item name and the prive. the quantity column shows "1" by default.....This is what may have to change in the text box4 and sent to E4. this process may continue for 2nd 3rd and so on ......other products scanned and for each the default quantity value might have to be changed.....
    I hope you understood

  • 1. File downloaded ... you can remove it ...


    2. Are you the author of all the macros ...?


    3. Do you know you are limited to 11 scans ...?


    4. If as you scan ... your program adds 1 as a default Quantity...


    Your need to adjust this quantity requires to locate the ROW being used by your latest scan ...


    Do we agree ???


    If we do ... could you test following patch:


    Code
    1. Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    2. ActiveSheet.Cells(ActiveCell.Row, 5) = TextBox4.Value
    3. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hi....
    2. No..not all, some help from forums, some from google search and some tweeked as per my need....Using this workbook for my shop and personnel use, Totally developed by me with the help from good people like you.
    3.Yes 11 scans are more than sufficient per client.
    4. Yes You are correct. program adds 1 as a default Quantity...Your need to adjust this quantity requires to locate the ROW being used by your latest scan ...
    5. Carim please also note that there is a similar quantity box which pops up when you click through the "manual entry" button left of the scan box....Please if you could fix that.


    Thanks.

  • Thanks for all your clarifications ...:smile:


    Have you tested :


    Code
    1. Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    2. ActiveSheet.Cells(ActiveCell.Row, 5) = TextBox4.Value
    3. End Sub

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • it goes in cell E2
    and for a fresh scan it replaces the value in the same cell "E2"


    I think you got something wrong.....
    After the first scan the default quantity "1" is added in "E4" (E4 is not the active cell)
    The second scan similarly adds the quantity to the corresponding cell "E5"
    and so on.....
    After a scan if the default quantity "1" is to be changed then the value is changed through the textbox 4...
    So this procedure of changing quantity may be required not for the first two scans .....but for the third scam which the falls in"E6" with the first two scan quantity remaining "1".......
    So may be you have to use the procedure and search for the last used cell in range "E4:E14"

  • Thanks to your latest clarifications ... the objective is very clear ...


    Within all your code, where are the instructions to store scanned code bar back to the active sheet ... ?

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)