Find the first empty textbox in excel userform and fill in

[dotchieJack]

Dear helpers,
I have a frame (frame 1) with 3 textboxes, (TB1,TB2 & TB3)
TB3 is the result of some calculations from TB1 & TB2, so far no problem, works fine.
But I have a second frame (frame2) with 5 texboxes (TB4->TB8)
The question is, I do a calculation in frame 1, the result (TB3) should go into the first empty texbox in frame 2. If TB4 is empty then result from TB3 in TB4, if TB4 is not empty then result from TB3 in TB5, if TB4 and TB5 are not empty then the result from TB3 in TB6 etc.
Please advice
Thanks

 

[GeraldDLT]

Newbie here. Not sure if this is what you're looking for.

Private Sub CommandButton1_Click()
If TextBox1.Value = "" Then
UserForm2.TextBox1.Value = UserForm1.TextBox1.Value
ElseIf TextBox2.Value = "" Then
UserForm2.TextBox2.Value = UserForm1.TextBox1.Value
ElseIf TextBox3.Value = "" Then
UserForm2.TextBox3.Value = UserForm1.TextBox1.Value
End If
End Sub

 

[dotchieJack]

Hello GeraldDTl,
Thank you for your reply, You set me on the right path,
This is the code, and it works fine
Keep up the good work and thank you.

Private Sub CommandButton5_Click()
On Error GoTo oops
TextBox19.Value = Round(Val(TextBox18.Value) / Val(TextBox17.Value), 4)
If Frame2.TextBox1.Value = "" Then
Frame2.TextBox1.Value = Frame1.TextBox19.Value
ElseIf Frame2.TextBox2.Value = "" Then
Frame2.TextBox2.Value = Frame1.TextBox19.Value
ElseIf Frame2.TextBox3.Value = "" Then
Frame2.TextBox3.Value = Frame1.TextBox19.Value
ElseIf Frame2.TextBox4.Value = "" Then
Frame2.TextBox4.Value = Frame1.TextBox19.Value
ElseIf Frame2.TextBox5.Value = "" Then
Frame2.TextBox5.Value = Frame1.TextBox19.Value
End If
For Each Ctrl In Frame1.Controls
    If TypeName(Ctrl) = "TextBox" Then
    Ctrl.Value = ""
    End If
Next Ctrl
TextBox17.SetFocus
oops: TextBox17.SetFocus
End Sub

 

[dotchieJack]

Correction,
This does the same job

Private Sub CommandButton5_Click()
On Error GoTo oops
TextBox19.Value = Round(Val(TextBox18.Value) / Val(TextBox17.Value), 4)
If TextBox1.Value = "" Then
TextBox1.Value = Frame1.TextBox19.Value
ElseIf TextBox2.Value = "" Then
TextBox2.Value = Frame1.TextBox19.Value
ElseIf TextBox3.Value = "" Then
TextBox3.Value = Frame1.TextBox19.Value
ElseIf TextBox4.Value = "" Then
TextBox4.Value = Frame1.TextBox19.Value
ElseIf TextBox5.Value = "" Then
TextBox5.Value = Frame1.TextBox19.Value
End If
For Each Ctrl In Frame1.Controls
    If TypeName(Ctrl) = "TextBox" Then
    Ctrl.Value = ""
    End If
Next Ctrl
TextBox17.SetFocus
oops: TextBox17.SetFocus
End Sub

 

[dotchieJack]

Second correction,
Shorter

Private Sub CommandButton5_Click()
On Error GoTo oops
TextBox19.Value = Round(Val(TextBox18.Value) / Val(TextBox17.Value), 4)
If TextBox1.Value = "" Then
TextBox1.Value = TextBox19.Value
ElseIf TextBox2.Value = "" Then
TextBox2.Value = TextBox19.Value
ElseIf TextBox3.Value = "" Then
TextBox3.Value = TextBox19.Value
ElseIf TextBox4.Value = "" Then
TextBox4.Value = TextBox19.Value
ElseIf TextBox5.Value = "" Then
TextBox5.Value = TextBox19.Value
End If
For Each Ctrl In Frame1.Controls
    If TypeName(Ctrl) = "TextBox" Then
    Ctrl.Value = ""
    End If
Next Ctrl
TextBox17.SetFocus
oops: TextBox17.SetFocus
End Sub

All other ideas to make this code shorter are welcome

 

[Chihiro]

You can do something like this.

Dim tbox As Control
For Each tbox In Frame2.Controls
    If tbox.Value = "" Then
        tbox.Value = TextBox19.Value
        Exit For
    End If
Next tbox

Edit: I'm assuming there's only textbox in Frame2.

 

[dotchieJack]

Dear Chihiro,
As always, thank you for your efforts, first of all, I want to thank GeraltDTl for the support again, your code seems to skip textbox 2 and 5, but I always want to learn and improve
File included: button 'berekenen U-waarden'

 

[Chihiro]

You actually have those TextBox in Frame3.

Also, it skips over TextBox2 since these boxes were not added to form in sequence. Reordered TextBox in Frame3.

See attached with modifications made.

 

[dotchieJack]

Chihiro, thank you for the perfect solution,this was

You actually have those TextBox in Frame3.

my mistake.

 

[GeraldDLT]

Welcome, mate.