Continue on next row in same cell VBA

[Wes4444]

Hi,

I have a userfrom that opens up on the click of a cell and then enters data into the active cell and then the cell to the right on the click of the Finish button:

Private Sub FinishButton_Click()
Application.ScreenUpdating = False

If Defect_Box.ListIndex = -1 Then
Cancel = 1
MsgBox "Select Defect Type"
Defect_Box.SetFocus
Exit Sub
End If

If Location_Box.ListIndex = -1 Then
Cancel = 1
MsgBox "Select Defect Location"
Location_Box.SetFocus
Exit Sub
End If

ActiveCell = Defect_Box.Value
ActiveCell.Offset(0, 1).Value = Location_Box.Value
Unload Me
Application.ScreenUpdating = False

I want to add a button that will add the data and then shift down a row, but still in the same cell for the next entry to be added.

Is this actually possible and if so how would it be done?

Thanks in Advance.

Wes

 

[My Aswer Is This]

Do you really need to use active cell?
We normally do not suggest this.
Why not Say Column "B" lastrow with data in column "B" plus one.
Would something like that work?

 

[Wes4444]

Unfortunately this column has blank cells for certain rows so it is often not the last row without data in that I want to be changing.

 

[My Aswer Is This]

Unfortunately this column has blank cells for certain rows so it is often not the last row without data in that I want to be changing.

So always shift down one row after entering data?
No matter if the cell is empty or not?

Is that what you want?

 

[Wes4444]

Sorry I don't think my intial expanation was clear. I don't want to shift down a row, I want to enter data in the next row inside the cell (as if you were pressing ALT+Enter in the cell).

 

[My Aswer Is This]

I saw you said that but thought you made a mistake saying that. So you want to keep adding data to the same cell. So you would not use Offset(1) Offset(1) means one cell down.
So you want to enter "George" into active cell then next time enter a line break and the enter "Bob" into same cell is that correct?

Sorry I don't think my intial expanation was clear. I don't want to shift down a row, I want to enter data in the next row inside the cell (as if you were pressing ALT+Enter in the cell).

 

[Wes4444]

Yes exactly that.

 

[My Aswer Is This]

Try this:

ActiveCell = ActiveCell & vbNewLine & Defect_Box.Value
ActiveCell.Offset(0, 1).Value = ActiveCell.Offset(0, 1).Value & vbNewLine & Location_Box.Value

 

[Wes4444]

That works perfect for adding the defect in the new line thankyou!

I'm now not sure how to add the first line in the cell as this code starts writing in the second line. Would I have to have a seperate button for the first entry?

Preferably I would have the code you just wrote for the 'Next Defect' Button and then that same code but with an Unload Me code at the end for the 'Done' Button.

 

[My Aswer Is This]

I just gave you the way to do this.
Now if you want to use one or two buttons to do this at least now you see how to add values to a cell.

If the active cell is empty when you start you may need to write a if statement.
Like if activecell.value="" then do this else do this.