Checking to see if a Range of Cells are Empty from Userform Multipage

[Hmerman]

Hello,
Hope you are well.

I have an userform multipage which has nine textboxes. When I click a button the code should check whether range A11:I11 is empty and place the values of the textboxes in the cells. If it is not empty the code is offset by one row and checks again. If the button is clicked it will only stop when it reaches the 21st row.

My problem is checking whether range A11:I11 is empty and offsetting it every time I click the button.<code>
<code>
<code>
Private Sub CommandButton4_Click()


'Find first empty cell within range and place Multipage Userform Objects content in first empty cell
Dim dfR As Range
Dim rwRng As Range
Dim conTrl As Object

Application.ScreenUpdating = False

For Each conTrl In Me.MultiPage1.Page2.Controls

</code></code>If TypeName(conTrl) = "TextBox" Or TypeName(conTrl) = "ComboBox" Then
<code><code>

i = i + 1

​

Set dfR = Sheets("Sheet1").Cells(11, i)​

Set rwRng = Sheets("Sheet1").Range("A11:I11")
​

If rwRng.Value = "" Then
​

dfR.Value = conTrl.Value
​

Else
​

If rwRng.Offset(1, 0).Value = "" Then
​

dfR.Offset(1, 0).Value = conTrl.Value
​

Else
​

dfR.End(xlDown).Select
​

If Selection.Row < 21 Then
​

Selection.Offset(1, 0).Value = conTrl.Value
​

Else
​

MsgBox "Space A11:I21 is full!"
​

Exit For

End If
​

End If
​

End If
​

End If
​

Next conTrl

Application.ScreenUpdating = True


End Sub
</code></code>

Can someone please help with the parts in red?

Regards
Herman
</code>

 

[Hmerman]

Solved it!

Instead of checking whether the whole row is empty, I just told the code to check if the control object (textbox or combobox) is empty and if it is place a " " (space) in the designated cell on the sheet1 in the loop. This makes the next loop think that a cell is not empty therefore offsetting to the correct row. Here is my code (the blue part):
<code>
<code>
Private Sub CommandButton4_Click()


'Find first empty cell within range and place Multipage Userform Objects content in first empty cell
Dim dfR As Range
Dim conTrl As Object

Application.ScreenUpdating = False

On Error Resume Next

For Each conTrl In Me.MultiPage1.Page2.Controls

If TypeName(conTrl) = "TextBox" Or TypeName(conTrl) = "ComboBox" Then
​

i = i + 1​

Set dfR = Sheets("Sheet1").Cells(11, i)
​

If conTrl.Value = "" Then
​

conTrl.Value = " "
​

End If
​

If dfR = "" Then
​

dfR.Value = conTrl.Value
​

Else
​

If dfR.Offset(1, 0) = "" Then
​

dfR.Offset(1, 0).Value = conTrl.Value
​

Else
​

dfR.End(xlDown).Select
​

If Selection.Row < 21 Then
​

Selection.Offset(1, 0).Value = conTrl.Value
​

Else
​

MsgBox "Space A11:I21 is full!"
​

Exit For

End If
​

End If
​

End If
​

End If
​

Next conTrl

On Error GoTo 0

Application.ScreenUpdating = True


End Sub
</code></code>

Regards
Herman