Add textbox value from userform in cell if cell is empty

[MrThor]

Hi, I want to add a textbox value in a cell if its empty. If the cell is not empty I want to add to the next cell under instead and so on. I have a code which I can not get so it works correctly. Can anybody help me with this?

Private Sub TextBox1_Change()

Dim rg As Worksheet
Set rg = Worksheets("Blad2")




If rg.Range("B2").Value = "" Then
rg.Range("B2").Value = Jan1.TextBox1.Value


End If




End Sub

 

[alansidman]

Maybe a loop

Option Explicit


Private Sub TextBox1_Change()


    Dim rg As Worksheet
    Set rg = Worksheets("Blad2")
    Dim i As Long, lr As Long
    lr = Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To lr
        If rg.Range("B" & i).Value = "" Then
            rg.Range("B" & i).Value = Jan1.TextBox1.Value
        Next i
    End If
End Sub

 

[smozgur]

Hi MrThor,

Welcome to MrExcel!

Change event wouldn't easily help in this case, because every time you type something in the text box, the cell value will be populated, and it won't be empty string even if you have something more to type in the text box.

If you don't have to use Change event for some specific reason, then I suggest using Textbox1_Exit event which will be triggered when you exit the textbox on the form (clicking on another box or button), or even using a command button to update the cell value on click.

Following uses the Exit event:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim sht As Worksheet
Dim cll As Range


Set sht = Worksheets("Blad2")
Set cll = sht.Range("B2")


Do Until cll.Value = ""
    Set cll = cll.Offset(1)
Loop


cll.Value = TextBox1.Value
End Sub

It is simply starting from B2 cell, and using a loop to check the cell value, and set the cell to be updated as the next row cell if the previous cell is not empty.

 

[MrThor]

Thank u very much smozgur, this solved my problem!

Reards

 

[smozgur]

You're welcome.

Following is an alternative, more optimized way instead testing all cells from B2.

This one is simply going to the last cell of the B column (Column index is 2), and jumping to the last used cell, then setting then next cell as the subject.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim sht As Worksheet
Dim cll As Range
    Set sht = Worksheets("Blad2")
    Set cll = sht.Cells(sht.Rows.Count, 2).End(xlUp).Offset(1)
    cll.Value = TextBox1.Value
End Sub

There are really more than one way to make things work in VBA and object oriented programming. We don't even have to use variables, instead use a single line code, but it wouldn't be a good practice of coding.

Glad to hear it helps.