MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

MsgBox format Question

  Asked By: Tye    Date: Dec 20    Category: MS Office    Views: 2017

Is there a way to force the message to be centered after using a (Chr
(10) command (line feed)?


Message = MsgBox(EmployeeName & " is not eligible for discount." &
Chr(10) & Chr(10) & _
"Over Maximum of Range", vbOKOnly, "Over Maximum of Range")

The second part of the text is left justified and I need it to be
centered. I know I could rig it, by adding spaces but wanted cleaner
code and the lenght of EmployeeName varies (thus the size of the box).
I don't want to use a form for this. I just need it to center.



1 Answer Found

Answer #1    Answered By: Miriam Green     Answered On: Dec 20

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 = "J" ' for testing

Application.ScreenUpdating = False
Set SelectedSheet = ActiveSheet
Set TempSheet = Sheets.Add
With TempSheet
.Range("A1") = EmployeeName & " is not eligible for discount."
HalfWidth = .Columns("A:A").ColumnWidth / 2
Application.DisplayAlerts = False
Application.DisplayAlerts = True
End With

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.

Didn't find what you were looking for? Find more on MsgBox format Question Or get search suggestion and latest updates.