Not easily, the Userform route is probably the easiest, but if you
insist on using a message box you could prefix the line you want to
appear centred with a number of spaces determined by the length of the
employees name.. not especially easy as you have to account for
kerning and for the fact the letters such as l and i are narrower than
letters such as w and m. I played with this for a while and got this
which works quite well (but I have stuck to default fonts throughout):
Sub blah()
EmployeeName = "John Smith of Harlow in Essex in England somewhere"
'EmployeeName = "iiiiiiiiiiiiiiiiiiiiiiiiiiiii" ' for testing
'EmployeeName = "WWWWWWWWWWWWWWWWWWWWWWWWWWWWW" ' for testing
'EmployeeName = "J" ' for testing
Application.ScreenUpdating = False
Set SelectedSheet = ActiveSheet
Set TempSheet = Sheets.Add
With TempSheet
.Range("A1") = EmployeeName & " is not eligible for discount."
.Range("A1").EntireColumn.AutoFit
HalfWidth = .Columns("A:A").ColumnWidth / 2
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
End With
SelectedSheet.Select
TimesToRepeat = (HalfWidth - 21 / 2) * 0.5785 - 0.2475
Application.ScreenUpdating = True
Message = MsgBox(EmployeeName & " is not eligible for discount." _
& Chr(10) & Chr(10) & _
Application.Rept(" ", TimesToRepeat * 3) _
& "Over Maximum of Range", vbOKOnly, "Over Maximum of Range")
Set TempSheet = Nothing
Set SelectedSheet = Nothing
End Sub
It puts the string (EmployeeName and " is not eligible for
discount.") into a cell on a temporarily added sheet and uses the
autofit command on that column, then using the resultant column width
value, calculates the number of spaces to prefix the second line with,
then does so with the Rept function. The temporarily added sheet is
deleted, and the previously selected sheet is reselected. Worked well
here with default fonts and a variety of different names.