Excel macro MsgBox to get user input?

Question

in a nut-shell, I'd like to have a cell populated with something that the user inputs.

I'd like to have a box pop up like, "Type in the name of the person you want to assign this work to:" Then the user types in a name, and that name is saved to a variable and used later on in the spreadsheet.

can it be done?

Answer 1

Below are three examples, one a messge box and two using an input box, that I use in a training class. You would have to tweak the code a little to fit it to your needs.

These macros were triggered by a button placed on the worksheet. You can just as easily create a custom menu item to call these.

There should be indentation in coding, but when entered here it strips it out.


Option Explicit

Sub UserMsgBox()
Dim aAnswer
Dim bAnswer
Dim sMsgTop As String
Dim sMsgMid As String

sMsgTop = "The Message Box is used to convey information to the user. For example:" & vbCrLf & vbCrLf
sMsgTop = sMsgTop & "You have pressed the Message Box button on the Example sheet." & vbCrLf & vbCrLf
sMsgTop = sMsgTop & "Would you like to continue?"

aAnswer = MsgBox(sMsgTop, vbInformation + vbYesNo, "Message Box Example")
If aAnswer = vbNo Then Exit Sub
If aAnswer = vbYes Then
sMsgMid = "A Message Box can give the user a choice on how to proceed. For example:" & vbCrLf & vbCrLf
sMsgMid = sMsgMid & "Do you really want to add this information?" & vbCrLf & vbCrLf

bAnswer = MsgBox(sMsgMid, vbQuestion + vbOKCancel, "Message Box Example")
If bAnswer = vbCancel Then Exit Sub
If bAnswer = vbOK Then
Range("I5:M30").Value = "Here is some data."
End If
Range("A1").Select
End If
End Sub

------------------------------------------------

Sub UserInputBox()
Dim rUserRange As Range

On Error GoTo Canceled

' It is bad formatting to run a line of code out to infinity.
' The way that a line is broken up into segments is to add an underscore " _"
' where you wish to break up the line.
'
' Look up InputBox Method in the VBA help for an explanation of the arguments.
Set rUserRange = Application.InputBox("The Input Box is used to provide a means for the user to supply required information. For example:" _
& vbCrLf & vbCrLf & "Select the range to erase, then click OK:", "Input Box Example", , , , , , Type:=8)
rUserRange.Clear
Range("A1").Select
Canceled:
End Sub

------------------------------------------------

Sub MyFifthMacro()
Dim sAnswer As String
Dim sMsg As String

On Error GoTo errMyFifthMacro
Err.Raise 11

PleaseTryAgain:
sAnswer = InputBox("Please enter name")
If sAnswer = "" Then
MsgBox "Please enter a name."
GoTo PleaseTryAgain
ElseIf IsNumeric(sAnswer) = True Then
MsgBox "The instructions call for a date. You entered: " & sAnswer & "."
GoTo PleaseTryAgain
Else
MsgBox "Hello " & sAnswer
End If

Exit Sub

errMyFifthMacro:
sMsg = sMsg & "Error type: " & Err.Number & vbCrLf
sMsg = sMsg & "Error discription: " & Err.Description & vbCrLf
sMsg = sMsg & "Error Location: MyFifthMacro"

MsgBox sMsg, , "Program Error"
End Sub

Answer 2

They are making it way too complicated. Below is a simple code that would put the entry in Cell A1. The variable the answer is saved to is Answer. However the variable goes bye bye after the subroutine is done unless you declare Answer as Static instead of as a Dim. So you'd declare:

Static Answer

Below is code to put the InputBox answer immediately into Cell A1.

Dim Answer
Dim Message As String

Message = "Type in the name of the person you want to assign this work to:"
Answer = InputBox(Message, "Work Assignment")

Range("A1").Value = Answer

Granted, that mine doesn't have all of the error handling stuff that there's does. But for something simple, you don't need all of that error handling.

Answer 3

why not use the 'comment' function? click on the cell for the input and click on 'insert' - 'comment' from the top menu. when the user places his mouse on the cell, a box opens up that displays your message. play with the size of the comment box so that all of the info shows on the mouse-over.