Find first empty cell in column and enter value from textbox

JB's solution is elegant and I'm certain will be that answer that you need, however the one thing I would mention is that it would enter the value in the next empty cell at the bottom of a column of data (ignoring any empty cells interspersed within the data). If you need to find the first empty cell then your piece of code is the one to utilise:

Please Login or Register  to view this content.

The code returned a value in the columns cells below the selected cell.

For example:
c
co
col
colu
colum
column

Private Sub TextBox1_Change()
Range("A2").End(xlDown).Offset(1, 0).value = TextBox1.value
ActiveCell.value = TextBox1.Text
End Sub

Hi Kevin,

That's the "Offset(1, 0).value" it's finding the last filled cell and then selecting the one below that, which is therefore the next empty cell.

Were you looking for a different action?

Yes, I want to write a person's name in the next empty cell in a column.

In this instance I tried getting the word "column" in one cell. Which it did after expending 5 cells above, but I'm asking to fill only one cell with one word. . Some might say I'm too picky, but then I watch my dog search for a place to poop.


VBAcode.png

Hi Kevin,

I see what you're doing there and why it's doing what it is.

You could change the way the input works so that it enters the value in the text box when you click out of it:

Please Login or Register  to view this content.

Or you can remove your macro entirely and then add an ActiveX Command Button to the sheet - then right click and select View Code.

Change the code to:

Please Login or Register  to view this content.

Also note that in both examples above you can replace this code:

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

These aren't going in the sheet, I'm trying to use them in a userform.

The command button was the best solution. Thank you.

Excellent, glad it's sorted and I've learnt a bit myself in the process

I'm entering data across one row at a time. Not all cells in a row need to be filled for my purposes. Cells B and C will always have a value.

I would like my userform to start writing on the next row down from a cell in B or C.

An option could be that the userform somehow fill in all empty cells in a row with "N/A" before or when it is closed. I could add a command button for that, but what would the code be?


excelprntscr.png

Please Login or Register  to view this content.