Input Form to Get Data and Store it in Another Tab in Excel



I used the page Input Form to Get Data and Store it in Another Tab in Excel to produce a form. 

VBA below:

Sub data_input()

ws_output = "Data"

next_row = Sheets(ws_output).Range("A" & Rows.Count).End(xlUp).Offset(1).Row

Sheets(ws_output).Cells(next_row, 1).Value = Range("employee_id").Value

Sheets(ws_output).Cells(next_row, 2).Value = Range("employee_name").Value

Sheets(ws_output).Cells(next_row, 9).Value = Range("resignation_date").Value

Sheets(ws_output).Cells(next_row, 10).Value = Range("notice_period").Value

Sheets(ws_output).Cells(next_row, 11).Value = Range("termination_date").Value

Sheets(ws_output).Cells(next_row, 13).Value = Range("leave_reason").Value

Sheets(ws_output).Cells(next_row, 14).Value = Range("vol_unvol").Value

Sheets(ws_output).Cells(next_row, 15).Value = Range("new_employer").Value

Sheets(ws_output).Cells(next_row, 16).Value = Range("termination_obligations").Value

Sheets(ws_output).Cells(next_row, 17).Value = Range("regretted").Value

End Sub

I now need to add VBA to make some of the cells mandatory on tab 1 (HRBP Form) and I would like a message to say "Submitted" when they click the Submit Macro. 

When clicking the Submit Macro the data is stored in tab 2 (Data). I also want to add a vlookup formula to the Data tab using Employee ID's from tab 3 (GMA Roster) - but due to the preceeding 0 this is not working. As you can see in the attached spreadsheet the cells have an error. Please can you assist and advise how to fix this issue? 



You should try using the solution that I provided as an answer to your last question. I showed you how to make fields mandatory, you just check if they are empty or not using an IF statement, or you can use data validation like one of the tutorials in the series that you reference talks about. If you have a question about the solution that I posted in your other question, just click the "Discuss" button under it and ask :)
Original Question
don (rep: 1969) Jul 21, '20 at 6:23 am
Thank you Don, I will try that. My biggest issue is the preceeding 0 (as above). Would you be able to advise a solution to that? 
missmasih (rep: 2) Jul 21, '20 at 6:34 am
Sure but there is no attached spreadsheet with your question; you can edit it and try to upload the spreadsheet again.
don (rep: 1969) Jul 21, '20 at 11:37 am
I have uploaded to the post. 
missmasih (rep: 2) Jul 21, '20 at 11:39 am
Add to Discussion



Here is the code I introduced to you yesterday, but applied to the setup you now published.

Option Explicit

Sub data_input()

    Dim Ws_Output       As Worksheet
    Dim Next_Row        As Long

    Set Ws_Output = Worksheets("Data")
    ' using the With statement:
    ' the object specified at "With ..." is connected to each of the lines
    ' in the block (ends with "End With") by a leading period,
    ' e.g. .Cells(Next_Row, 1) refers to Ws_Output
    ' but Cells(Next_Row, 1), without leading period, refers to the ActiveSheet.
    ' Observe the necessary leading period in .Rows.Count
    With Ws_Output
        Next_Row = .Range("A" & .Rows.Count).End(xlUp).Row + 1

        ' all of the named ranges are on the ActiveSheet (no leading period)
        .Cells(Next_Row, 1).Value = Range("employee_id").Value
        .Cells(Next_Row, 2).Value = Range("employee_name").Value
        .Cells(Next_Row, 9).Value = Range("resignation_date").Value
        .Cells(Next_Row, 10).Value = Range("notice_period").Value
        .Cells(Next_Row, 11).Value = Range("termination_date").Value
        .Cells(Next_Row, 13).Value = Range("leave_reason").Value
        .Cells(Next_Row, 14).Value = Range("vol_unvol").Value
        .Cells(Next_Row, 15).Value = Range("new_employer").Value
        .Cells(Next_Row, 16).Value = Range("termination_obligations").Value
        .Cells(Next_Row, 17).Value = Range("regretted").Value
    End With
End Sub

Private Sub ClearFormFields()

    Const AllFields As String = "employee_id,employee_name,resignation_date,notice_period" & _
                                "termination_date,leave_reason,vol_unvol,new_employer" & _
    Dim Fld() As String
    Dim i As Integer

    Fld = Split(AllFields, ",")
    For i = 0 To UBound(Fld)
        Range(Fld(i)).Value = ""
    Next i
End Sub

Please study the comments I added. After you understand the above code you might advance to use the code below which is doing exactly the same thing - but differently, and perhaps more efficiently. I have also included the provision for creating mandatory fields which we discussed in an earlier thread.

Option Explicit

Sub data_input()
    ' 070

    Const AllFields As String = "employee_id,employee_name,resignation_date,notice_period," & _
                                "termination_date,leave_reason,vol_unvol,new_employer," & _

    Dim Ws_Output       As Worksheet            ' "Data" tab
    Dim Fld()           As String               ' array of named range names
    Dim Clms            As Variant              ' target columns (match with Fld)
    Dim i               As Integer              ' index to both Fld() and Clms()
    Dim Rt              As Long                 ' target row
    Dim Miss            As Range                ' missing entry

    Fld = Split(AllFields, ",")
    ' match sequence of column numbers to array Fld()
    Clms = Array(1, 2, 9, 10, 11, 13, 14, 15, 16, 17)
    Set Miss = MissingEntry(Fld)
    If Miss Is Nothing Then
        Set Ws_Output = Worksheets("Data")
        With Ws_Output
            Rt = .Range("A" & .Rows.Count).End(xlUp).Row + 1
            For i = 0 To UBound(Fld)
                .Cells(Rt, Clms(i)).Value = Range(Fld(i)).Value
                Range(Fld(i)).Value = ""
            Next i
        End With
        MsgBox "'" & Miss.Offset(-1).Value & "' is a mandatory field." & vbCr & _
               "Please make an appropriate entry.", _
               vbExclamation, "Missing entry"
    End If
End Sub

Private Function MissingEntry(Fld() As String) As Range
    ' 070

    ' list the fields which are NOT mandatory
    Const Options As String = "resignation_date,notice_period," & _
                              "termination_date,leave_reason,vol_unvol,new_employer," & _
    Dim f           As Integer      ' loop index of Fld()

    For f = 0 To UBound(Fld)
        If Range(Fld(f)) = "" Then
            If InStr(1, Options, Fld(f), vbTextCompare) = 0 Then
                ' if the missing entry is not optional
                Set MissingEntry = Range(Fld(f))
                Exit For
            End If
        End If
    Next f
End Function

The trade-off you can observe is between preparation and code volume. The more time you invest in setting up an environment in which your code can run the less code you will need. The time given and saved is often nearly the same. But you permanently gain efficiency and save on maintenance effort .



Thank you for this Varius. 
I have applied this coding but do not get a message box appear when clicking the submit macro. Please can you help on this? 
And also my issue with the preceeding 0 on tab 3 (GMA Roster). I need to fix that so that I can set up a vlookup using that as the lookup value. 
missmasih (rep: 2) Jul 22, '20 at 4:35 am
I have added the provision for mandatory fields to the compresed solution offered above. As for the leading zeroes, they are a separate problem not covered in this thread.
Without further explanation from you, I think you shouldn't bother about them. (1) Format the cells as Custom 0000000 so that they display leading zeroes. (2) The cell value will be the number, without leading zeroes and you can use that number as a search criterium in VLOOKUP. (3) Convert the number entered as text in the form to a number within the above code (add an IF condition in the loop). If you want to leave the number as text for some reason change it to a number in the VLOOKUP formula, like =VLOOKUP(Val(A18), etc.
Variatus (rep: 4864) Jul 22, '20 at 8:55 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login