Excel VBA: Form to be filled in with data from another worksheet

[NLU_VBA]

Hello,

In an Excel file I have 2 worksheets:

• Worksheet A: a form to be filled in with 3 fields: Incident iD (C1), Name of the reporter (C2), Description of the incident (C3) + a button "OK"
• Worksheet B: a table with the same fields as in worksheet A, but displayed as a row:
  • A1: Title n°1= Incident iD, B1: Title n°2= Name of the reporter, C1: Title n°3= Description of the incident;
  • A2: Incident iD for the first recorded incident, B2: reporter's name for the first recorded incident, C2: Description of the first recorded incident;
  • A3: Incident iD of the second recorded incident...

I would like a VBA code that works like following:
When entering an Incident iD in cell C1 from worksheet A and pressing the button Enter,

• If the Incident iD is already recorded in the table from worksheet B, all data linked to that Incident iD must be automatically displayed for the 2 other fields:
  • Name of the reporter= the name of the employee who recorded the incident linked to the entered Incident iD;
  • Description of the incident = the description of the incident related to the entered Incident iD.
• if the Incident iD is not yet recorded in the table, nothing happens

Can someone help me please?

 

[AlphaFrog]

To install the code...

1. Right-click on Worksheet A's tab and select View Code from the pop-up context menu.
2. Paste the code from below in the worksheet code module (not in a standard code module.)
3. In the code, change the sheet name of "Sheet2" to the actual tab name of Worksheet B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Range
    If Target.Address = "$C$1" Then
        Set Found = Worksheets("Sheet2").Columns("A").Find(Target, , xlValues, 1, 1, 1, 0)
        If Not Found Is Nothing Then
            Me.Range("C2").Value = Found.Offset(0, 1).Value
            Me.Range("C3").Value = Found.Offset(0, 2).Value
        End If
    End If
End Sub

 

[NLU_VBA]

To install the code...

1. Right-click on Worksheet A's tab and select View Code from the pop-up context menu.
2. Paste the code from below in the worksheet code module (not in a standard code module.)
3. In the code, change the sheet name of "Sheet2" to the actual tab name of Worksheet B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Range
    If Target.Address = "$C$1" Then
        Set Found = Worksheets("Sheet2").Columns("A").Find(Target, , xlValues, 1, 1, 1, 0)
        If Not Found Is Nothing Then
            Me.Range("C2").Value = Found.Offset(0, 1).Value
            Me.Range("C3").Value = Found.Offset(0, 2).Value
        End If
    End If
End Sub

Hello @AlphaFrog, thanks for your response.
Still trying to write your code, as the original case has more parameters...
In your code, is "Me" the first worksheet?

 

[NLU_VBA]

To install the code...

1. Right-click on Worksheet A's tab and select View Code from the pop-up context menu.
2. Paste the code from below in the worksheet code module (not in a standard code module.)
3. In the code, change the sheet name of "Sheet2" to the actual tab name of Worksheet B.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Found As Range
    If Target.Address = "$C$1" Then
        Set Found = Worksheets("Sheet2").Columns("A").Find(Target, , xlValues, 1, 1, 1, 0)
        If Not Found Is Nothing Then
            Me.Range("C2").Value = Found.Offset(0, 1).Value
            Me.Range("C3").Value = Found.Offset(0, 2).Value
        End If
    End If
End Sub

When adding a value in C1 and pressing the button "Enter", nothing is displayed in Worksheet A

 

[MikeVol]

AlphaFrog​

gave you the code for a sheet event that is entered into the sheet module and that is triggered when the value in cell C1 changes. If you need the code to work when you press a button, you can slightly modify it like:

Option Explicit

Sub NLU_VBA()
    Dim Found       As Range

    If Worksheets("Sheet1").Range("$C$1") <> "" Then
        Set Found = Worksheets("Sheet2").Columns("A").Find(Worksheets("Sheet1").Range("$C$1"), , xlValues, 1, 1, 1, 0)

        If Not Found Is Nothing Then
            Worksheets("Sheet1").Range("C2").Value = Found.Offset(0, 1).Value
            Worksheets("Sheet1").Range("C3").Value = Found.Offset(0, 2).Value
        Else
            MsgBox "No matches! ", vbExclamation
        End If

    End If

End Sub

Enter this code into the standard module. Good luck.

 

[NLU_VBA]

AlphaFrog​

gave you the code for a sheet event that is entered into the sheet module and that is triggered when the value in cell C1 changes. If you need the code to work when you press a button, you can slightly modify it like:

Option Explicit

Sub NLU_VBA()
    Dim Found       As Range

    If Worksheets("Sheet1").Range("$C$1") <> "" Then
        Set Found = Worksheets("Sheet2").Columns("A").Find(Worksheets("Sheet1").Range("$C$1"), , xlValues, 1, 1, 1, 0)

        If Not Found Is Nothing Then
            Worksheets("Sheet1").Range("C2").Value = Found.Offset(0, 1).Value
            Worksheets("Sheet1").Range("C3").Value = Found.Offset(0, 2).Value
        Else
            MsgBox "No matches! ", vbExclamation
        End If

    End If

End Sub

Enter this code into the standard module. Good luck.

Many thanks @MikeVol for your response.
I tried, but nothing happened...

 

[MikeVol]

Many thanks @MikeVol for your response.
I tried, but nothing happened...

I don't believe you!

 

[NLU_VBA]

I don't believe you!

I adapted your code to the data I have on my side; I copy/paste here:

Sub Worksheet_Change(ByVal Target As Range)
Dim Found As Range

'Set references to the worksheets
Set wsForm = ThisWorkbook.Sheets("1.Form - draft")
Set wsDatabase = ThisWorkbook.Sheets("2.HI database - automation")

If Worksheet("wsForm").Range("$C$5") <> "" Then
Set Found = wsDatabase.Columns("A").Find(wsForm.Range("$C$5"), , x1Values, 1, 1, 1, 0)

If Not Found Is Nothing Then
wsForm.Range("C6").Value = Found.Offset(0, 1).Value
wsForm.Range("C7").Value = Found.Offset(0, 2).Value
wsForm.Range("C8").Value = Found.Offset(0, 3).Value
wsForm.Range("C9").Value = Found.Offset(0, 4).Value
wsForm.Range("C10").Value = Found.Offset(0, 5).Value
wsForm.Range("C11").Value = Found.Offset(0, 6).Value
wsForm.Range("C12").Value = Found.Offset(0, 7).Value
wsForm.Range("C13").Value = Found.Offset(0, 8).Value
wsForm.Range("C17").Value = Found.Offset(0, 9).Value
wsForm.Range("C18").Value = Found.Offset(0, 10).Value
wsForm.Range("C19").Value = Found.Offset(0, 11).Value
wsForm.Range("C20").Value = Found.Offset(0, 12).Value
wsForm.Range("C21").Value = Found.Offset(0, 13).Value
wsForm.Range("C25").Value = Found.Offset(0, 14).Value
wsForm.Range("C29").Value = Found.Offset(0, 15).Value
wsForm.Range("C30").Value = Found.Offset(0, 16).Value
wsForm.Range("C34").Value = Found.Offset(0, 17).Value
wsForm.Range("C38").Value = Found.Offset(0, 18).Value
wsForm.Range("C39").Value = Found.Offset(0, 19).Value
wsForm.Range("C40").Value = Found.Offset(0, 20).Value
wsForm.Range("C44").Value = Found.Offset(0, 21).Value
wsForm.Range("C48").Value = Found.Offset(0, 22).Value
wsForm.Range("C49").Value = Found.Offset(0, 23).Value
wsForm.Range("C50").Value = Found.Offset(0, 24).Value
wsForm.Range("C53").Value = Found.Offset(0, 25).Value
wsForm.Range("C54").Value = Found.Offset(0, 26).Value
wsForm.Range("C58").Value = Found.Offset(0, 27).Value
wsForm.Range("C59").Value = Found.Offset(0, 28).Value
wsForm.Range("C64").Value = Found.Offset(0, 29).Value
wsForm.Range("C65").Value = Found.Offset(0, 30).Value
wsForm.Range("C66").Value = Found.Offset(0, 31).Value
Else
MsgBox "No matches!", vbExclamation
End If
End If
End Sub

Is there anything missing that was in your code, and not in mine?

 

[MikeVol]

Well, you still have to study and study the material part...In my message #5 that I wrote to you about the procedures, I wonder if you will find your mistake yourself? You wrote a procedure that does not start with a button!

 

[MikeVol]

NLU_VBA​

Try next example file.