VBA - Use message/input box response to populate cell

[rachel06]

Hi!

I've tried Googling this and I'm getting all sorts of crazy results, so I'm bringing this to the experts who've helped me so much!

I'd like to have two message/input boxes pop up when the spreadsheet is opened. One that says "Please enter your name" and one that says "Please enter your Run ID". Then, I'd like those inputs to populate cells C36 (name input) and C37 (run ID).

Is this possible to do in Excel?

Thanks so much!!

 

[Joe4]

Put this VBA code in the "ThisWorkbook" module in your workbook in VBA:

Private Sub Workbook_Open()

    Dim nm As String
    Dim id As String
    
    nm = InputBox("Please enter your name")
    id = InputBox("Please enter your Run ID")
    
    Range("C36").Value = nm
    Range("C37").Value = id
    
End Sub

As long as VBA code is enabled for that computer, this will run automatically whenever the workbook is first opened.

 

[dmt32]

Hi
try following & see if does what you want

place in ThisWorkBook code page

Private Sub Workbook_Open()
    Dim strPrompt       As String
    Dim i               As Long
    Dim Entry(1 To 2)   As Variant
  
    i = 1
    Do
        strPrompt = Choose(i, "Name", "Run ID")
        Entry(i) = InputBox("Please enter your " & strPrompt, strPrompt)
        'cancel pressed
        If StrPtr(Entry(i)) = 0 Then Exit Sub
        If Len(Entry(i)) > 0 Then i = i + 1
    Loop Until i > 2
  
    ThisWorkbook.Worksheets("Sheet1").Range("C36:C37").Value = Application.Transpose(Entry)
End Sub

change the Sheet name shown in BOLD as required

Dave

 

[rachel06]

Put this VBA code in the "ThisWorkbook" module in your workbook in VBA:

Private Sub Workbook_Open()

    Dim nm As String
    Dim id As String
   
    nm = InputBox("Please enter your name")
    id = InputBox("Please enter your Run ID")
   
    Range("C36").Value = nm
    Range("C37").Value = id
   
End Sub

As long as VBA code is enabled for that computer, this will run automatically whenever the workbook is first opened.

Perfect as always. Thanks so much!! My team should have VBA enabled, but if they don't, I'm assuming they'll get the enable prompt at the top of the window when they open the workbook?

 

[Joe4]

You are welcome.

Yes, they should get that prompt, depending on the Security settings.

 

[Bosquedeguate]

Or you might consider a simple User form with both entries ...

VBA code for the above UserForm

Private Sub btnSubmit_Click()
    Dim Entry(1 To 2)   As Variant
 
    Entry(1) = txtName
    Entry(2) = txtRunID
 
    ThisWorkbook.Worksheets("Sheet1").Range("C36:C37").Value = Application.Transpose(Entry)
    UserForm1.Hide
    Unload UserForm1
End Sub

Private Sub Workbook_Open()
  UserForm1.Show
End Sub

 

[rachel06]

Or you might consider a simple User form with both entries ...
View attachment 96793
VBA code for the above UserForm

Private Sub btnSubmit_Click()
    Dim Entry(1 To 2)   As Variant
 
    Entry(1) = txtName
    Entry(2) = txtRunID
 
    ThisWorkbook.Worksheets("Sheet1").Range("C36:C37").Value = Application.Transpose(Entry)
    UserForm1.Hide
    Unload UserForm1
End Sub

Private Sub Workbook_Open()
  UserForm1.Show
End Sub

Oooh. I like this too. Is there any way to tweak the one that Joe4 provided to have it in one message box? It's just a bit simpler and I've actually changed some things already:

Private Sub Workbook_Open()

    Dim nm As String
    Dim id As String
    
    nm = InputBox("Please enter your name")
    id = InputBox("Please enter your Run ID")
    
    Range("F5").Value = "Prepared By: " & nm
    Range("F6").Value = "Run ID: " & id
    
End Sub

 

[Bosquedeguate]

Oooh. I like this too. Is there any way to tweak the one that Joe4 provided to have it in one message box? It's just a bit simpler and I've actually changed some things already:

Private Sub Workbook_Open()

    Dim nm As String
    Dim id As String
   
    nm = InputBox("Please enter your name")
    id = InputBox("Please enter your Run ID")
   
    Range("F5").Value = "Prepared By: " & nm
    Range("F6").Value = "Run ID: " & id
   
End Sub

That is basically what I did. I used Joe4’s code with the Submit button. Workbook_Open () simply opens the form.

 

[Joe4]

Oooh. I like this too. Is there any way to tweak the one that Joe4 provided to have it in one message box? It's just a bit simpler and I've actually changed some things already:

Private Sub Workbook_Open()

    Dim nm As String
    Dim id As String
  
    nm = InputBox("Please enter your name")
    id = InputBox("Please enter your Run ID")
  
    Range("F5").Value = "Prepared By: " & nm
    Range("F6").Value = "Run ID: " & id
  
End Sub

No, the Input Box method only allows one input.
So unless you asked the question something like: "Please enter your name and Run ID separated by a comma", and then parsed the answer, if you want just one object, you would need to use a User Form. Just note that you have to manually create that User Form, if you go that method.

 

[rachel06]

Okay. Thanks all! I'll do some playing around. One way or another, I have what I need.

Thanks so much!!!