try this:
Private Sub CommandButton1_Click()
    Dim nextrow As Long
    Dim ctrl As Control
    
    Dim ws As Worksheet
   
    If bcomplete Then
    
        Set ws = Worksheets("Sheet2")
        ws.Select
        
        'determine the next empty row
        nextrow = FindLastRow(ws, "A") + 1
        
        'transfer the control values
        
        For Each ctrl In Me.Controls
            If ctrl.Visible Then
                
                If TypeName(ctrl) = "TextBox" Then
                    
                    ws.Cells(nextrow, 1) = ctrl.Value
                    nextrow = nextrow + 1
                End If
            End If
        Next ctrl
        
        Unload Me
    End If
End Sub
Function FindLastRow(ByVal ws As Worksheet, ColumnLetter As String) As Long
    FindLastRow = ws.Range(ColumnLetter & Rows.Count).End(xlUp).Row
End Function