Announcement

Collapse
No announcement yet.

Adding a value from text box to the last used cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.
    Code:
    Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Dim LastRow As Long, ws As Worksheet
    
       Set ws = ActiveSheet
    
       LastRow = ws.Range("E" & Rows.Count).End(xlUp).Row + 1
    
       ws.Range("E" & LastRow).Value = TextBox4.Value
     
     
    
    End Sub
    Above code isn't working the way i want to.
    Help will be appreciated

  • #2
    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 ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

    Comment


    • #3
      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

      Comment


      • #4
        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 ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

        Comment


        • #5
          Hi Carim,
          Sorry ..but doesn't work. Tried both ways :Range("E4").End(xlDown) + 1 and :Range("E14").End(xlUp) + 1 The value from text box is sent to Range E26

          Comment


          • #6
            Hello,

            My crystal ball is very limited ...

            Could you attach your file ...
            If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

            Comment


            • #7
              Hi, yes sure..

              Comment


              • #8
                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

                Comment


                • #9
                  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 ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                  Comment


                  • #10
                    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

                    Comment


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

                      Comment


                      • #12
                        Originally posted by zubinnajmi View Post
                        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 ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                        Comment


                        • #13
                          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

                          Comment


                          • #14
                            Have you Downloaded the file....Should i Remove the uploaded file....it has some sensitive information

                            Comment


                            • #15


                              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:
                              Private Sub TextBox4_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
                                  ActiveSheet.Cells(ActiveCell.Row, 5) = TextBox4.Value
                              End Sub
                              If you feel like saying "Thank You" for the help received ...You can click on the "Like" icon ...just underneath ... ... in the bottom right corner ...

                              Comment

                              Working...
                              X