Concatenate & format standard text in cell A1, Sheet1 with strings from two input boxes

Try this: I assume that Budget Summary is "Sheet1", if not replace with correct sheet name.

Please Login or Register  to view this content.

This is what I have so far but it does not include the NLCAB text. I am going to try Kiran.Sunkara's code as I can see it addresses all the conditions I am trying to incorporate. - mandora


Sub EnterProgramNameAndYear()
Dim y As String, x As String

Do Until Len(y) > 0
y = InputBox("Enter Program Name", "Enter Program Name using Title Case")
If Len(y) = 0 Then MsgBox "You Must Enter Program Name", 16, "Message"
Loop

Do Until Len(x) > 0
x = InputBox("Enter Program Year", "Enter Program Year as yyyy")
If Len(x) = 0 Then MsgBox "You Must Enter Program Year", 16, "Message"
Loop

Range("A1") = y & " - " & x

End Sub

I adopted most of Kiran.Sunkara's code with minor changes in some prompts and message box header text for clarity. I added two vbOKOnly controls and dropped the suggested formating portion since this can be set directly on the sheet. If someone should want to edit the formating without going into vba, thats fine. I will happily give Kiran.Sunkara a positive rating!


Public Sub btnEnterInfoIntoWorkbook()

Dim strProgramName As String
Dim strProgramYear As String

'Get the program name and budget year.
strProgramName = InputBox(Prompt:="Type the name of the program in Title Case.", Title:=" Enter Program Name in Title Case")
strProgramYear = InputBox(Prompt:="Type the program's budget year as yyyy", Title:=" Enter Program's Budget Year as yyyy")

'Proceed only if both input boxes have text in them.
If strProgramName = "" Or strProgramYear = "" Then
MsgBox "YOU DID NOT ENTER ALL REQUIRED INFORMATION." _
& vbNewLine & vbNewLine & "Edit header in cell ""A1"" of the Budget Summary.", _
vbInformation + vbOKOnly, " Confirmation of Budget Summary Header Information"
Else
MsgBox "You entered: " & strProgramName & " - " & strProgramYear & "" _
& vbNewLine & vbNewLine & "Edit header, as needed, in cell ""A1"" of the Budget Summary.", _
vbInformation + vbOKOnly, " Confirmation of Budget Summary Header Information"

Sheet1.Range("A1") = "NCCAB BUDGET SUMMARY: " & WorksheetFunction.Proper(strProgramName) & " - " & strProgramYear

' Sheet1.Range("A1").Select
' With Selection.Font
' .Name = "Arial"
' .Size = 20
' .Strikethrough = False
' .Superscript = False
' .Subscript = False
' .OutlineFont = False
' .Shadow = False
' .Underline = xlUnderlineStyleNone
' .ColorIndex = 1
' End With
' Selection.Font.Bold = True

End If
End Sub

Thanks for your willingness to help patel45. I did try Kiran.Sunkara's recommendation and it worked so well that problem is solved.