Unload form past data to worksheet clear form ready for next.

[glossy001]

I hope some body can help me with this code
I have created a form and want to enter data into the form, the form needs to find the last row on a spreadsheet offset to the next row past data into several differents cells (all on the same row), clear the form ready for the next lot of data.
Private Sub CmdBtnEnterData_Click()
'Dim RowCount As 0
Dim RowCount As Long
Dim ctl As Control
'check user input
If Me.TxtBxDate.Value = "" Then
MsgBox "please enter the date"
Me.TxtBxDate.SetFocus
End If
If Me.TxtBxInfo.Value = "" Then
MsgBox "Please insert the details of the task as completed "
Me.TxtBxInfo.SetFocus
End If

If Me.CmboBxSite.Value = "" Then
MsgBox "Insert details of your location when the task was completed "
Me.CmboBxSite.SetFocus
End If
If Me.CmboBxProjTitle.Value = "" Then
MsgBox "Insert a project title for the task "
Me.CmboBxProjTitle.SetFocus
End If
If Me.CmboBxJob.Value = "" Then
MsgBox "what sort of work was this "
Me.CmboBxJob.SetFocus
End If
If Me.CmboBxServiceObj.Value = "" Then
MsgBox "The service objective this task falls under "
Me.CmboBxServiceObj.SetFocus
End If
'Write Data to the worksheet and clear the form ready for next input of data
'RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
'RowCount = 0("Data").Range("A1").CurrentRegion.Rows.Count
'RowCount = COUNTA(A:A)("Data").Range("A1").CurrentRegion.Rows.Count
'RowCount = ("Data").Range("A1").CurrentRegion.Rows.Count
'RowCount0 = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

RowCount = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count

With Worksheets("Data").Range("A1")
.Offset(RowCount, 0).Value = Me.TxtBxDate.Value
.Offset(RowCount, 1).Value = Me.TxtBxInfo.Value
.Offset(RowCount, 2).Value = Me.CmboBxSite.Value
.Offset(RowCount, 3).Value = Me.CmboBxProjTitle.Value
.Offset(RowCount, 4).Value = Me.CmboBxJob.Value
.Offset(RowCount, 5).Value = Me.CmboBxServiceObj.Value
.Offset(RowCount, 6).Value = Me.CmboBxObjectives.Value
.Offset(RowCount, 7).Value = Me.CmboBxActionSteps.Value
.Offset(RowCount, 8).Value = Me.CmboBxExpPerform.Value
.Offset(RowCount, 9).Value = Me.CmboBxExceedPer.Value

End With
'clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "textbox" Or TypeName(ctl) = "comboBox" Then ctl.Value = ""
'elself Typename (ctl) = "checkbox" Then ctl.Value = False
'elself Typename (ctl) = "checkbox" Then ctl.Value = False
'elself Typename (ctl) = "checkbox" Then ctl.Value = False

End If
Next ctl
End Sub

As you can see I have left in the bits I tried but didn't use

many thanks to any body that can help (as you can see I'm not an expert)

 

[ztodd]

So what problems are you having with it?

 

[glossy001]

So what problems are you having with it?

'clear the form
For Each ctl In Me.Controls
If TypeName(ctl) = "textbox" Or TypeName(ctl) = "comboBox" Then ctl.Value = ""
'elself Typename (ctl) = "checkbox" Then ctl.Value = False
'elself Typename (ctl) = "checkbox" Then ctl.Value = False
'elself Typename (ctl) = "checkbox" Then ctl.Value = False

End If
error message is
compile error:
End If without block If

 

[AlphaFrog]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CmdBtnEnterData_Click()
    
    [color=darkblue]Dim[/color] ctl    [color=darkblue]As[/color] Control
    
    [color=green]'check user input[/color]
    [color=darkblue]Select[/color] [color=darkblue]Case[/color] [color=darkblue]True[/color]
        [color=darkblue]Case[/color] Me.TxtBxDate.Value = ""
            MsgBox "please enter the date"
            Me.TxtBxDate.SetFocus
            
        [color=darkblue]Case[/color] Me.TxtBxInfo.Value = ""
            MsgBox "Please insert the details of the task as completed "
            Me.TxtBxInfo.SetFocus
            
        [color=darkblue]Case[/color] Me.CmboBxSite.Value = ""
            MsgBox "Insert details of your location when the task was completed "
            Me.CmboBxSite.SetFocus
            
        [color=darkblue]Case[/color] Me.CmboBxProjTitle.Value = ""
            MsgBox "Insert a project title for the task "
            Me.CmboBxProjTitle.SetFocus
            
        [color=darkblue]Case[/color] Me.CmboBxJob.Value = ""
            MsgBox "what sort of work was this "
            Me.CmboBxJob.SetFocus
            
        [color=darkblue]Case[/color] Me.CmboBxServiceObj.Value = ""
            MsgBox "The service objective this task falls under "
            Me.CmboBxServiceObj.SetFocus
            
        [color=darkblue]Case[/color] [color=darkblue]Else[/color]   [color=green]'Entries validated[/color]
            
            [color=darkblue]With[/color] Worksheets("Data").Range("A" & Rows.Count).End(xlUp)
                .Offset(1, 0).Value = Me.TxtBxDate.Value
                .Offset(1, 1).Value = Me.TxtBxInfo.Value
                .Offset(1, 2).Value = Me.CmboBxSite.Value
                .Offset(1, 3).Value = Me.CmboBxProjTitle.Value
                .Offset(1, 4).Value = Me.CmboBxJob.Value
                .Offset(1, 5).Value = Me.CmboBxServiceObj.Value
                .Offset(1, 6).Value = Me.CmboBxObjectives.Value
                .Offset(1, 7).Value = Me.CmboBxActionSteps.Value
                .Offset(1, 8).Value = Me.CmboBxExpPerform.Value
                .Offset(1, 9).Value = Me.CmboBxExceedPer.Value
            [color=darkblue]End[/color] [color=darkblue]With[/color]
            
            [color=green]'clear the form[/color]
            [color=darkblue]For[/color] [color=darkblue]Each[/color] ctl [color=darkblue]In[/color] Me.Controls
                [color=darkblue]If[/color] TypeName(ctl) = "TextBox" [color=darkblue]Or[/color] TypeName(ctl) = "ComboBox" [color=darkblue]Then[/color] ctl.Value = ""
            [color=darkblue]Next[/color] ctl
            
    [color=darkblue]End[/color] [color=darkblue]Select[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[glossy001]

Thanks very much for you help
I have added your modified code and all seems to work except this error message

'Entries validated
With Worksheets("Data").Range("A" & Rows.Count).End(xlUp)
'runtime error 9
'subscript out of rang
'rows.count=1048576

.Offset(1, 0).Value = Me.TxtBxDate.Value
.Offset(1, 1).Value = Me.TxtBxInfo.Value
.Offset(1, 2).Value = Me.CmboBxSite.Value
.Offset(1, 3).Value = Me.CmboBxProjTitle.Value
.Offset(1, 4).Value = Me.CmboBxJob.Value
.Offset(1, 5).Value = Me.CmboBxServiceObj.Value
.Offset(1, 6).Value = Me.CmboBxObjectives.Value
.Offset(1, 7).Value = Me.CmboBxActionSteps.Value
.Offset(1, 8).Value = Me.CmboBxExpPerform.Value
.Offset(1, 9).Value = Me.CmboBxExceedPer.Value
End With
Can you help
thanking you

 

[AlphaFrog]

Do you have a worksheet called Data?

 

[glossy001]

Hi AlphaFrog
Yes I have a worksheet called Data this is the sheet the form paste's results to
Many thanks for taking the time to help me sort this out.

 

[AlphaFrog]

I'm not sure what's wrong. The code syntax is correct.

Does the sheet have a trailing space at the end of its' tab name ("Data ")?

 

[glossy001]

you were correct there was a trailing space well done the whole form works just fine.
Once again many thanks for taking the time to help me.
regards
GG