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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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,109
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,109
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,325
Messages
5,547,263
Members
410,783
Latest member
sonnny
Top