Announcement

Collapse
No announcement yet.

Transfer Text Box Values to each pasted range of Cells in Worksheet

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

  • Transfer Text Box Values to each pasted range of Cells in Worksheet



    Happy Holidays All:

    Trying to paste some text box values from a form onto a spreadsheet but only where a pasted range of values has been placed.

    Scenario:

    1. User clicks a command button to start Entry.
    2. The form pops up with pre-populated text box values.
    3. User enters comments in an empty text box
    4. User clicks submit button to record the transaction.

    At step 4 from this point the form will copy the values that were pre-selected before the form was initialized and paste them into a designated worksheet called "ACTIVITYLOG" (which it is doing)
    THEN it should paste all the text box values from the form into the columns adjacent to the pasted values which I can't get it to do. This needs to happen for each transaction without overwriting the previous transactions.

    The code resides within the form as follows:

    Sub MoveToLog()

    Application.ScreenUpdating = False

    'STEP 1: Copy and paste filtered range of cells from other worksheet to Activity Log worksheet...THIS PART WORKS FINE.

    With Sheets("ENTERPRISE")
    Range("ENTERPRISE[ID],ENTERPRISE[Risk Score],ENTERPRISE[Due Date],ENTERPRISE[Due In Days]").Copy '<----- Add ranges as required
    Sheets("ACTIVITYLOG").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlValues
    End With
    '-------------------------------------------------------------------------------
    'STEP 2: Copy textbox values from form into empty cells adjacent to pasted range from above (starting in column E then column D, etc) HERE'S MY STICKING POINT.

    With Sheets("ACTIVITYLOG")
    Do
    If IsEmpty(ActiveCell) = False Then
    .Range("A2").End(xlDown).Offset(0, 4).Value = TextBox1.Value '<---goes to first empty cell in column E next to first pasted row of data and should copy down to last pasted row of data
    .Range("A2").End(xlDown).Offset(0, 6).Value = TextBox2.Value '<---goes to first empty cell in column F next to first pasted row of data and should copy down to last pasted row of data
    .Range("A2").End(xlDown).Offset(0, 7).Value = TextBox3.Value '<---goes to first empty cell in column G next to first pasted row of data and should copy down to last pasted row of data
    .Range("A2").End(xlDown).Offset(0, 8).Value = TextBox4.Value '<---goes to first empty cell in column H next to first pasted row of data and should copy down to last pasted row of data
    .Range("A2").End(xlDown).Offset(0, 9).Value = TextBox5.Value '<---goes to first empty cell in column I next to first pasted row of data and should copy down to last pasted row of data
    End If
    Loop Until IsEmpty(ActiveCell) = True
    End With

    Application.ScreenUpdating = True

    End Sub



    Any insights would be helpful!

    Thank you

  • #2
    Hey Taylor,
    If you are closing the form before trying to set the cell values textbox values no longer exist, I do not know if that is what is happening in your case though. I usually hide the form and unload on finish so that the values are always available. I also reference the form directly as in .Range("A2")..... = Form1.TextBox1.Value because as I understand it in a with loop the information from the form is not available without direct reference.

    Comment


    • #3
      Happy New Year Bigfoot.

      Thanks for the tip. I'll give it shot.

      Comment


      • #4


        Hello Taylor,

        Have you tried to reference the form directly? I've been doing my project (automation of the Excel tool for a service desk queries tracker) and need to copy the content from a textbox to another worksheet row. What is the easiest way to do this?

        Cheers,
        Steve

        Comment

        Working...
        X