Formatting text differently within a Msgbox

[Guest]

Thanks to Herald for his previous help! I wanted to know if it was possible
to format text within a Msgbox differently -- ie -- the first line below I'd
like to have a font of 20 with the text bolded. The other lines I'd like to
have at a font of 12 with no bolded text. Is it possible to do this? Thanks!

MsgBox "INTERMOD CALC" & vbNewLine & _
" " & vbNewLine & _
"Spreadsheet creator: Robert" & vbNewLine & _
" " & vbNewLine & _
"Chief Project Advisor: Lisa" & vbNewLine & _
" " & vbNewLine & _
"Beta Testers: Richard, Lisa," & vbNewLine & _
"George, Kelly, Michael,"

 

[William Benson]

I do not think you can change fonts and stuff of messagebox text. Maybe with
a Win API call, not through Excel. If you were to put these items in a cell,
yes, you could change a cell's appearance mid-cell.


Sub Macro4()
'Example turns any code after a linebreak to red size 14 bold
'Note: Need to define MyRange as something
'Also, this assumes only one linebreak, you would need
'Special parsing routine to change text in between more
'than one line break

Dim Cell As Range
For Each Cell In Selection
If InStr(Cell.Value, Chr(10)) > 0 Then
With Cell.Characters _
(Start:=InStr(Cell.Value, Chr(10)) + 1, _
Length:=Len(Cell.Value) - _
InStr(Cell.Value, Chr(10))).Font
.Size = 14
.ColorIndex = 3
.Bold = True
End With
End If
Next Cell
End Sub




"Linking to specific cells in pivot table"

 

[Henry]

The easiest way round this is to create a form with two labels and a command
button.

Make the form look like a msgbox, or use brighter colours to draw attention
to it.
Set one label to font size 20, and bold text.
Set the other to font size 12, and normal text.
Set both captions to "".
Change the command button caption to OK.
For the CommandButton1_Click event:

Private Sub CommandButton1_Click()
Unload Me
End Sub

Put a Funtion in a module as follows:

Function MyMsgSub(MyLabel1 as String, Mylabel2 as String, MyTitle as String)
'*****************************************
'UDF to show my message box
'*****************************************
MyMsgBox.Caption = MyTitle 'Set form title
MyMsgBox.Label1.Caption = MyLabel1 'Set label1 text
MyMsgBox.Label2.Caption = MyLabel2 'Set label2 text
MyMsgBox.Show 'Show Form
End Function


You can now

Call MyMsgSub("This is the first, larger, text.","This is the second,
smaller text.","Demonstration")

Each time you call the function you can send it the text you want to appear,
just like a MsgBox

HTH
Henry


"Linking to specific cells in pivot table"

 

[William Benson]

I like this!

The easiest way round this is to create a form with two labels and a
command button.

Make the form look like a msgbox, or use brighter colours to draw
attention to it.
Set one label to font size 20, and bold text.
Set the other to font size 12, and normal text.
Set both captions to "".
Change the command button caption to OK.
For the CommandButton1_Click event:

Private Sub CommandButton1_Click()
Unload Me
End Sub

Put a Funtion in a module as follows:

Function MyMsgSub(MyLabel1 as String, Mylabel2 as String, MyTitle as
String)
'*****************************************
'UDF to show my message box
'*****************************************
MyMsgBox.Caption = MyTitle 'Set form title
MyMsgBox.Label1.Caption = MyLabel1 'Set label1 text
MyMsgBox.Label2.Caption = MyLabel2 'Set label2 text
MyMsgBox.Show 'Show
Form
End Function


You can now

Call MyMsgSub("This is the first, larger, text.","This is the second,
smaller text.","Demonstration")

Each time you call the function you can send it the text you want to
appear, just like a MsgBox

HTH
Henry


"Linking to specific cells in pivot table"

 

[Henry]

William,

Thanks for your appreciation.

You can do all sorts of fancy things with it.
I usually pass a two digit number to the routine.
The first digit is stripped off to set the background colour and the second
digit determines what sort of controls are shown on the form.
I put all the controls on the form at design time but make them Visible =
False.
There's no reason why you can't pass a three, four or five digit number,
using the other digits to set the foreground colour, font size,. etc., etc.
I often use this trick to replace the MsgBox and InputBox with my own
design.
E.G. Red background for fatal errors, blue background for warnings, pastel
shade background for Input boxes.
Call MyMsgSub("You haven't completed the Surname box.","Cannot
continue.",11)
11 represents a red background (first1) and OK button visible (second1).
Call MyMsgSub("Please input a Surname.","Input required",23)
22 represents a blue background (first digit, 2) and a textbox (for input)
and the OK button both visible (second digit, 3).
You have to ensure that you return a value from this.
You can also make the box any size you want, even full 'Excel window' sized,
covering any other forms that are open.

You are limited to what you can do only by the limits of an Excel form and
your imagination.

Henry