Q:

Iterations on a userform

I have a userform that I need to get users to enter multiple sets of tasks, as well as an estimate of the time that it will take to undertake each task.
After each task, and time estimate, I would like for the information to be entered on a spreadsheet, and the fields become blank again, for the next task to be entered.
This is the main code:
Global i As Integer ' Rows
Global j As Integer ' Columns
Global tCount As Integer ' Task Count

Sub Time_Calcs()

Dim mcHours As Integer ' M/C process in hours
Dim hDays As Integer ' Hours available per day

i = 2
j = 3
tCount = 1
hDays = 6

Worksheets("Calculations").Activate
Cells.Delete

i = i + 1
Cells(i, 2) = "Item"
Cells(i, 3) = "Task"
Cells(i, 4) = "# of iterations"
Cells(i, 5) = "Maker"
Cells(i, 6) = "Checker"

i = i + 1

TaskForm.Show

End Sub

I have 2 questions:
1) How can I get the code to loop through a series of instructions, such as taking the information from the form, and putting it into a spreadsheet, before clearing the data for the next task to be entered? I've tried this code, but it only seems to work for one iteration.
Private Sub CommandButton1_Click()

j = 3

Cells(i, 2) = tCount

While j <= 6

    If j = 3 Then
        Cells(i, j) = TaskName
    ElseIf j = 4 Then
        Cells(i, j) = NoofIts
    ElseIf j = 5 Then
        Cells(i, j) = mTime
    ElseIf j = 6 Then
        Cells(i, j) = cTime
    End If

    j = j + 1

Wend

i = i + 1
j = 3
tCount = tCount + 1

'MSForms.Control(TaskName).Value = vbNullString
'MSForms.Control(NoofIts).Value = vbnullstrins
'MSForms.Control(mTime).Value = vbNullString
'MSForms.Control(cTime).Value = vbNullString

TaskName = vbNullString
NoofIts = vbNullString
mTime = vbNullString
cTime = vbNullString

End Sub

2) After I enter data, I would like the user to be able to TAB to the next box. Currently, if I hit TAB, it TABs the cursor right. How do I get it to enable moving to the next box/button via the TAB button?

A:

You should have a couple of procedures to add the data to the worksheet and to clear the form of existing data.  
Clearing the form is just a case of going through each control on the form and settings it's value to some default - usually Null.  
Private Sub Reset()

    Dim ctrl As Control

    For Each ctrl In Me.Controls
        Select Case TypeName(ctrl)
            Case "TextBox", "ComboBox"
                ctrl.Value = Null
            Case "OptionButton"
                ctrl.Value = False
            Case Else
                'Do nothing
        End Select
    Next ctrl

End Sub

Saving the form data can be quite complicated depending on the checks you want to make before allowing the data to be transferred.
I make use of the Tag property of a control to store the column number is should be saved in and the data type of the data.
So something like 16;CCur would indicate it will be saved in column 16 as currency. 
The actual code to save the data would start with finding the last cell on the worksheet containing data - this can then be used to place the new data on the next available row. 
Each control is then checked and the data saved to the column indicated by in the tag property.  
After all the data has been saved the form is reset and the initialize routine executed
Private Sub btnSave_Click()

    Dim rLastCell As Range
    Dim ctrl As Control
    Dim lCol As Long
    Dim wrkSht As Worksheet

    Set wrkSht = ThisWorkbook.Worksheets("Raw Data")

    Set rLastCell = wrkSht.Cells(wrkSht.Rows.Count, 1).End(xlUp).Offset(1)

    For Each ctrl In Me.Controls
        With ctrl
            If TypeName(ctrl) = "TextBox" Or TypeName(ctrl) = "ComboBox" Then
                If Trim(ctrl.Value) <> "" Then
                    If InStr(.Tag, ";") > 0 Then
                        lCol = Split(.Tag, ";")(0)

                        'Decide which data type to use.
                        Select Case Split(.Tag, ";")(1)
                            Case "CLNG"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CLng(ctrl.Value)
                            Case "CCur"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CCur(ctrl.Value)
                            Case "CDATE"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CDate(ctrl.Value)
                            Case "CSTR", "CSENTENCE"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CStr(ctrl.Value)
                            Case "CDBL"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CDbl(ctrl.Value)
                            Case "CPER"
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)) = CDbl(ctrl.Value) / 100
                                    wrkSht.Cells(rLastCell.Row, CLng(lCol)).NumberFormat = "0.00%"
                            Case Else

                        End Select
                    End If
                End If
            End If
        End With
    Next ctrl

    Reset
    UserForm_Initialize

End Sub

I've added the UserForm_Initlialize procedure as it sets up the form for data entry - todays date is entered in a control, combo-boxes are set up, a label is given a caption showing the current Value Added Tax amount from a named range and the correct control is given focus:  
Private Sub UserForm_Initialize()

    Me.TextBox1 = Format(Date, "dd-mmm-yyyy")
    Me.lblVAT = "VAT @ " & Format$(ThisWorkbook.Names("VAT").RefersToRange, "Percent")

    With Me.ComboBox1
        .AddItem "A"
        .AddItem "B"
        .AddItem "C"
    End With

    Me.TextBox1.SetFocus

End Sub  

I have extensions to the code - code that automatically converts names to propercase, doesn't allow more than 2 decimal places or only allows whole numbers.  There's also code to check that required data has been entered and highlight the controls which are missing data before saving to the sheet.  That would take a whole lot more to explain though.

