CODEWITHSUNDEEP
excelvba
Red
Red

Reputation: 61

Why is user form data not entered row by row?

User form data is overwriting itself rather than entering row by row.

I have tried different combinations of ActiveCell, Cell, Set, and Range. Pulling code from the internet for somewhat similar purposes and tweaking was unsuccessful. 

Private Sub CheckOut_Click()

Dim xCell As Range
For Each xCell In ActiveSheet.Columns(1).Cells
If Len(xCell) = 0 Then
xCell.Select
Exit For
End If
Next

Range("B2").Value = TextBox1.Text
Range("C2").Value = TextBox2.Text
Range("D2").Value = ("OUT")
TextBox1 = ""
TextBox2 = ""

End Sub

I want each submission in the user form to populate a new row creating a list. What is actually happening is everything writes to row 2.

Please give feed back with down votes and flags.

Upvotes: 1

Views: 89

Answers (2)

VBasic2008
VBasic2008

Reputation: 54817

Your code

Private Sub CheckOut_Click()

    Dim xCell As Range

    ' Loop through cells of first column. You can also use "A" instead of 1.
    ' Since you haven't used ActiveSheet with the ranges, it is also not
    ' needed here. It would be better to specify the worksheet e.g. "Sheet1".
    For Each xCell In ActiveSheet.Columns(1).Cells
        ' Check if length of value of current cell is 0.
        ' Usually 'If xCell = "" Then' is used.
        If Len(xCell) = 0 Then
            ' Select (Go to) current cell. What for?
            xCell.Select
            ' Exit the For Next loop. Will jump to 'Range("B2") ...'
            Exit For
        End If
    Next

    ' Write certain values to cells of 2nd row.
    Range("B2").Value = TextBox1.Text
    Range("C2").Value = TextBox2.Text
    Range("D2").Value = ("OUT")
    ' Clear the Text boxes.
    TextBox1 = ""
    TextBox2 = ""

End Sub

In a nutshell, the code will check for an empty cell in column 1, will select the found cell (for an unknown reason) and will write some data to certain cells in the 2nd row and clear the values of the text boxes.

Questions

You loop through column 1. When an empty cell is found, do you want to write the values to columns B-D in the first found empty row or to columns B-D in the same row where the empty cell in column 1 was found?

Empty or Same?

Do you want this to happen only once, when an empty cell was found or for all found empty cells in the used range of column 1?

Once or All?

The used range of column 1 would be from e.g. A1 or which cell you choose to the last used cell.
You can manually determine the last used cell by selecting the last cell of column 1 ("A") and holding RIGHT CTRL and pressing UP. This will be done in the code, but it is just for you to have a visual of what will be checked for empty cells if you want to find more of them.

A1 or ...?

You should address these questions in your question which you can modify by using the edit button below it.

Possible Solution

Private Sub CheckOut_Click()

    Dim xCell As Range  ' Current Cell in Column "A"
    Dim FER As Long     ' First Empty Row

    ' Loop through cells of Column "A".
    For Each xCell In Columns("A")
        ' Check if value of Current Cell is "".
        If xCell.Value = "" Then
            ' Select Current Cell. If necessary.
            xCell.Select
            ' Calculate First Empty Row using column "B".
            FER = Range("B" & Rows.Count).End(xlUp).Row + 1
            ' Write values to Target Cells in First Empty Row.
            Range("B" & FER).Value = TextBox1.Text
            Range("C" & FER).Value = TextBox2.Text
            Range("D" & FER).Value = ("OUT")
            ' Clear the Text boxes.
            TextBox1 = ""
            TextBox2 = ""
            Exit For ' Stop looping.
        End If
    Next

End Sub

Remarks

How would this make any sense?

What if OP didn't tell us that xCell.Select triggers a Selection Change event which will write values to xCell and the text boxes and will restrict this to the used range of column A?

Upvotes: 0

Schutt
Schutt

Reputation: 1134

Dim ws as Worksheet
Dim freeRow as long
Set ws = ActiveSheet 'Is it really necessary to use the active sheet?
freeRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).row + 1 'End(xlUp) jumps to the next free cell

ws.Cells(freeRow, 2) = TextBox1.Text
ws.Cells(freeRow, 3) = TextBox2.Text
ws.Cells(freeRow, 4) = "OUT"
TextBox1.Text = ""
TextBox2.Text = ""

It is usually considered that ".select" is bad practice since it can lead to weird/nasty errors - rather use variables (this makes your code more reusable and less error prone!)

Upvotes: 3

Related Questions