Add textbox value from userform in cell if cell is empty

MrThor

New Member
Joined
Aug 13, 2018
Messages
36
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?

Code:
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,937
Office Version
  1. 2019
Platform
  1. Windows
Maybe a loop

Code:
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

BatCoder
Joined
Feb 28, 2002
Messages
1,108
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:

Code:
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.
 

smozgur

BatCoder
Joined
Feb 28, 2002
Messages
1,108
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.

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,314
Messages
5,547,169
Members
410,775
Latest member
alal1030
Top