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

glossy001

New Member
Joined
Sep 18, 2014
Messages
13
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)
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

glossy001

New Member
Joined
Sep 18, 2014
Messages
13
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
Code:
[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

New Member
Joined
Sep 18, 2014
Messages
13

ADVERTISEMENT

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
 

glossy001

New Member
Joined
Sep 18, 2014
Messages
13

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,451
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

New Member
Joined
Sep 18, 2014
Messages
13
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,874
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top