Message Box VBA needed

[rusted314]

Hi,
I am working on a Macro and I need assistance with a message box for the report. I would like the macro to prompt the user by saying " What Month and Year are you running?" The user will then enter something like January 2013. The Macro will then take the input and insert it into cell D1 with "Month:" in front of it so the finished data will say "Month: January 2013".

I have the following macro to create the message box but don't know how to insert the input value into cell D1 with the word "Month:" in front of it. the whole thing should be in bold font.
Thanks

Sub MSG_box()
Dim MyInput As String
MyInput = InputBox("What Month and Year are you running?", _
"MyInputTitle", "Enter Month and Year ie. January 2013")
If MyInput = "Enter your input text HERE" Or _
MyInput = "" Then
Exit Sub
End If

End Sub

 

[CodeNinja]

Hi,
I would be careful with what you are attempting to do. You may get results you do not want as an inputbox does not really force data. If you want to restrict the user in some way, you will need to code that....

The example below should give you an idea of what you need to do:

Sub ForceInput()
Dim str As String

str = ""
While str <> "January" And str <> "February" And str <> "March" _
  And str <> "April" And str <> "May" And str <> "June" And str <> "July" _
  And str <> "August" And str <> "September" And str <> "October" And _
  str <> "November" And str <> "December"
  
    str = InputBox("Enter the month.")
Wend
Sheet1.Cells(1, "D") = "Month: " & str

End Sub

With the code you have above, you will need to parse (separate) out the month, but you really don't know how they will enter it... will they enter January, Jan or 1 or some other way. With the code above, you have a little more control although you may wish to tell the user how to enter it (IE they could be typing 1 over and over and get frustrated as it doesn't tell them to enter "January").

 

[rusted314]

Thank you for the quick reply. This is good feedback. The majority of the time I will be running the report (unless I am on vacation) so I will enter it correctly. I tried your query and I did get the message box but it didnt insert my response into cell D1. Thoughts?

 

[CodeNinja]

Rusted,
Not sure how that would not put your response into cell D1... the line

Sheet1.Cells(1, "D") = "Month: " & str

should do exactly that... put "Month: " & your response in cell D1 of sheet1.

If you are not working on sheet1, you may want to try:

Sheets(sheet name in double quotes).Cells(1, "D") = "Month: " & str</pre>

 

[rusted314]

Awesome. Thank you. The "Month: " & MyInput is what I was looking for. I got it to work with the code below. Appreciate the assistance.


Sub MSG_box()
Dim MyInput As String
MyInput = InputBox("What Month and Year are you running?", _
"MyInputTitle", "Enter Month and Year ie. January 2013")
Range("D1").Select
ActiveCell.FormulaR1C1 = "Month: " & MyInput

End Sub