Creating user prompted text boxes to fill out an excel sheet for an internship

Hello everyone,

I am currently interning and i am trying to create an excel spreadsheet to automate the form filling process for one of my companys departments. I need to create a spread sheet that when you open it, automated text boxs pop up and prompt the user to answer a variety of questions which in turn will fill out different cells on the excell spreadsheet.

My current macro code is :

Sub TestMacro()
Range("A2").Select
ActiveCell.FormulaR1C1 = InputBox("Name")
Range("B2").Select
ActiveCell.FormulaR1C1 = InputBox("Project #")
Range("C2").Select
ActiveCell.FormulaR1C1 = InputBox("Date")
Range("D2").Select
ActiveCell.FormulaR1C1 = InputBox("Problem")
Range("E2").Select
ActiveCell.FormulaR1C1 = InputBox("Solution")
End Sub

I am seeking help on 2 more companents of the code.

How do i code to fill out cells on a different sheet within the excel document?

How do i prompt the macros to run as soon as the sheet is opened rather then me having to manually run the macro?

ANy help would be greatly appreciated!