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.