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?

 

[NLU_VBA]

NLU_VBA​

Try next example file.

Cannot download it, as the link redirected me to a weird page

 

[MikeVol]

Cannot download it, as the link redirected me to a weird page

I don't believe you!

 

[AlphaFrog]

@NLU_VBA

Your code in post #8 looks sound. You just need to put it in the correct code module and rename the Sub.

The code I originally provided would automatically run when you made a change to cell C5 on worksheet A (wsForm). If you want it to work when you click a command button, you need to move the code to a different code module depending on the type of button you created on the sheet.

You can use a Form control button or a command button (an ActiveX control) to run a macro that performs an action when a user clicks it. Both these buttons are also known as a push button, which can be set up to automate the printing of a worksheet, filtering data, or calculating numbers. In general, a Form control button and an ActiveX control command button are similar in appearance and function. However, they do have a few differences, which are explained in the following link
https://support.microsoft.com/en-us/office/assign-a-macro-to-a-form-or-a-control-button-d58edd7d-cb04-4964-bead-9c72c843a283

If it is the Form-type button, paste your code in a standard code module (e.g. Module1), and change the first line to something like this...
Sub MyLookup()
You could name the macro whatever you like. Next, right-click on the button and select Assign Macro from the pop-up context menu and select MyLookup.

If it is an Active-X type button, right-click on it and select View Code. That will take you to the worksheet code module. Your code should already be there per my original post. Change the first line to this to run the code when you click the button.
Private Sub CommandButton1_Click()
...where Command1 is the name of your button (not the Caption).

 

[NLU_VBA]

I don't believe you!

@MikeVol , as I communicate with my company laptop, I got a warning message in a red popup, and this laptop with restrictions doesn't allow the access to this kind of websites

And yes, I have a lot to learn regarding VBA, this is the reason why I contact VBA experts to help me.

 

[NLU_VBA]

@NLU_VBA

Your code in post #8 looks sound. You just need to put it in the correct code module and rename the Sub.

The code I originally provided would automatically run when you made a change to cell C5 on worksheet A (wsForm). If you want it to work when you click a command button, you need to move the code to a different code module depending on the type of button you created on the sheet.

You can use a Form control button or a command button (an ActiveX control) to run a macro that performs an action when a user clicks it. Both these buttons are also known as a push button, which can be set up to automate the printing of a worksheet, filtering data, or calculating numbers. In general, a Form control button and an ActiveX control command button are similar in appearance and function. However, they do have a few differences, which are explained in the following link
https://support.microsoft.com/en-us/office/assign-a-macro-to-a-form-or-a-control-button-d58edd7d-cb04-4964-bead-9c72c843a283

If it is the Form-type button, paste your code in a standard code module (e.g. Module1), and change the first line to something like this...
Sub MyLookup()
You could name the macro whatever you like. Next, right-click on the button and select Assign Macro from the pop-up context menu and select MyLookup.

If it is an Active-X type button, right-click on it and select View Code. That will take you to the worksheet code module. Your code should already be there per my original post. Change the first line to this to run the code when you click the button.
Private Sub CommandButton1_Click()
...where Command1 is the name of your button (not the Caption).

Thanks @AlphaFrog for your response! I really appreciate it!
I will take my time to follow your instructions and will come back to you.

 

[MikeVol]

Ok, another option: You should have prepared the example file, not me.