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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
5,932
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,104
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,104
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,068
Messages
5,545,795
Members
410,708
Latest member
SanTrapGamer
Top