Hi. For two fields using this method, you'd have to be careful because there's the potential for your database to have a mismatch of first and last names should a mistake be made.
I've included two options below.
The first is using the same worksheet_change event that I've modified to include cells A1 and B1 on Feuil1 to capture the first and last names. Again, this is the more risky option where a mismatch could easily occur in your database.
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'variable declarations
Dim wb As Workbook: Set wb = ThisWorkbook
Dim inputWS As Worksheet: Set inputWS = wb.Sheets("Feuil1")
Dim dbaseWS As Worksheet: Set dbaseWS = wb.Sheets("feuil2")
Dim kCell As Range: Set kCell = inputWS.Range("A1:B1")
Dim lrow As Long
'catches a change to kCell
If Not Application.Intersect(kCell, Range(Target.Address)) Is Nothing Then
'Since there are now two fields, need to make sure that the code stops if
'there are more than one cells selected to avoid debug (i.e.; cells.count > 1)
If Not Selection.Cells.Count > 1 Then
'will only record name if kCell is not blank
If Not Target.Value = "" Then
'finds next open row in dbaseWS
If Target.Column = 1 Then
'Captures change to column 1 for first name
If dbaseWS.Cells(1, 1).Value = "" Then
'will assign first row if no data in dbaseWS yet
lrow = 1
Else
'will assign next open row if dbaseWS has data
lrow = dbaseWS.Cells(dbaseWS.Rows.Count, 1).End(xlUp).Row + 1
End If
dbaseWS.Cells(lrow, 1).Value = Target.Value
ElseIf Target.Column = 2 Then
'Captures change to column 2 for last name
If dbaseWS.Cells(1, 2).Value = "" Then
'will assign first row if no data in dbaseWS yet
lrow = 1
Else
'will assign next open row if dbaseWS has data
lrow = dbaseWS.Cells(dbaseWS.Rows.Count, 2).End(xlUp).Row + 1
End If
dbaseWS.Cells(lrow, 2).Value = Target.Value
End If
End If
End If
End If
End Sub
And the second is to use a way for the user to confirm their submission. This will be done using a button on the Feuil1, which will write the names to Feuil2.
On the actual Feuil1, you'd want go to the Developer tab and insert a button, see below. Then draw the button in cell C1 or D1, which ever is better for you. After you draw the button, you should see a "Assign Macro" dialog display. Click
New. You'll be taken to a newly created normal Module (not associated with any specific sheet) where you can add the below code to. This should ensure that the same customer's first and last name appear on the same row of the database (Feuil2).
View attachment 81619View attachment 81620
VBA Code:
Sub Button1_Click()
'variable declarations
Dim wb As Workbook: Set wb = ThisWorkbook
Dim inputWS As Worksheet: Set inputWS = wb.Sheets("Feuil1")
Dim dbaseWS As Worksheet: Set dbaseWS = wb.Sheets("feuil2")
Dim lrow As Long
'Will require both a first name and last name
If inputWS.Cells(1, 1) = vbNullString Or inputWS.Cells(1, 2) = vbNullString Then
MsgBox "Please include both a first and last name before continuing." _
, vbCritical, "Missing Data"
Exit Sub
End If
'Determines row to paste new customer data in
If dbaseWS.Cells(1, 1).Value = "" Then
'will assign first row if no data in dbaseWS yet
lrow = 1
Else
'will assign next open row if dbaseWS has data
lrow = dbaseWS.Cells(dbaseWS.Rows.Count, 1).End(xlUp).Row + 1
End If
dbaseWS.Cells(lrow, 1).Value = inputWS.Cells(1, 1)
dbaseWS.Cells(lrow, 2).Value = inputWS.Cells(1, 2)
End Sub
