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)
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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,276
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,276
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,858
Messages
5,525,236
Members
409,637
Latest member
LT TASL

This Week's Hot Topics

Top