Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Centre text in MsgBox in Excel
- Thread starter elise
- Start date
DaleWatson123321
Programmer
elise,
I see no one has "jumped" at this one. It would be nice if someone out there can come up with a SPECIFIC solution to the TWO questions you posed.
In the meantime, let me "get the ball rolling" by offering this simple "workaround" for centering a message. It's not perfect, but at least it might be useful. It uses the following code, in which you will have to adjust the variable "spaces" for the number of spaces to "pad" to the left of the text you want to center in the Message Box.
Dim msg As String
Dim spaces As Double
Dim lead_spaces as String
Dim fulltext As String
Sub Set_MessageBox()
spaces = 3
'For the number of leading spaces before your message.
'Change this to adjust for length of "msg" (below).
msg = "Testing This Message"
'Enter your message here.
lead_spaces = Application.WorksheetFunction.Rept(" ", spaces)
fulltext = lead_spaces & msg
MsgBox fulltext
End Sub
A couple of footnotes:
1) This code is really only applicable when the text length will be something less than about 20 characters. This is because of the "default minimum size" of the Message Box. Any text in EXCESS of 20 characters will automatically EXPAND the width of the Message Box. Therefore, in such cases, you would NOT require the above special routine, or if you do use it, you will want to use "spaces = 0" to eliminate any leading spaces.
2) I "played" with writing a complex formula which dealt with the length of a message entered in a worksheet cell, and a lookup table to adjust for the number of leading spaces required. However, I soon realized that, because of the varying widths of characters, and the use of "space" characters, it is really impossible to come up with a formula which will always center the text perfectly.
It's unfortunate that Excel doesn't have a built-in option to center the text.
Hopefully, someone will "enlighten" us that there is a better solution, including one for the insertion of a PICTURE into the Message Box.
Anyway, I hope this serves some purpose, perhaps even to let you know that you've NOT been neglected.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
I see no one has "jumped" at this one. It would be nice if someone out there can come up with a SPECIFIC solution to the TWO questions you posed.
In the meantime, let me "get the ball rolling" by offering this simple "workaround" for centering a message. It's not perfect, but at least it might be useful. It uses the following code, in which you will have to adjust the variable "spaces" for the number of spaces to "pad" to the left of the text you want to center in the Message Box.
Dim msg As String
Dim spaces As Double
Dim lead_spaces as String
Dim fulltext As String
Sub Set_MessageBox()
spaces = 3
'For the number of leading spaces before your message.
'Change this to adjust for length of "msg" (below).
msg = "Testing This Message"
'Enter your message here.
lead_spaces = Application.WorksheetFunction.Rept(" ", spaces)
fulltext = lead_spaces & msg
MsgBox fulltext
End Sub
A couple of footnotes:
1) This code is really only applicable when the text length will be something less than about 20 characters. This is because of the "default minimum size" of the Message Box. Any text in EXCESS of 20 characters will automatically EXPAND the width of the Message Box. Therefore, in such cases, you would NOT require the above special routine, or if you do use it, you will want to use "spaces = 0" to eliminate any leading spaces.
2) I "played" with writing a complex formula which dealt with the length of a message entered in a worksheet cell, and a lookup table to adjust for the number of leading spaces required. However, I soon realized that, because of the varying widths of characters, and the use of "space" characters, it is really impossible to come up with a formula which will always center the text perfectly.
It's unfortunate that Excel doesn't have a built-in option to center the text.
Hopefully, someone will "enlighten" us that there is a better solution, including one for the insertion of a PICTURE into the Message Box.
Anyway, I hope this serves some purpose, perhaps even to let you know that you've NOT been neglected.
Regards, ...Dale Watson dwatson@bsi.gov.mb.ca
- Status
Similar threads
- Locked
- Question