Popup Message Box with custom font color

onexc · Dec 10th 2016

Hello Ozgrid Members,

I'd like to create a workbook with some buttons, when they are clicked, a message box will be displayed with some info. However, I would like to change font color and background color of the message box, also the box should be resized to the length of the text in. I have been looking for it through the web for several days. All I found is that it can be done with "userform" feature of the excel, but I could not get find a sample. Please any help will be appreciated.

Thank you in advance.

Kind regards,

Onexc

**KjBox** · Dec 10th 2016

Re: Popup Message Box with custom font color

This should get you started.

This procedure is in the Worksheet Object Module and is aggigned to each button

Code

Sub Message()


    With frmMessage
        If Application.Caller = "Button 1" Then
            .lblMessage = "This is the message for Button 1."
        ElseIf Application.Caller = "Button 2" Then
            .lblMessage = "This is the message for Button 2, this is a much longer message, but on one line."
        ElseIf Application.Caller = "Button 5" Then
            .lblMessage = "This is the message for Button 3." & vbLf & "This will be on a second line"
        End If
        .Width = .lblMessage.Width + 30
        .cmdOK.Left = .Width - (.Width / 2) - 21
        .Show
    End With
    
End Sub

Display More

**Kenneth Hobson** · Dec 10th 2016

Re: Popup Message Box with custom font color

Have you never made a Userform before? It would have a TextBox control with Multiline property set to True and a CommandButton control. There are many tutorials that explain how to do it. This link shows the steps to do what you want, sort of. https://msdn.microsoft.com/en-…rary/office/ff840656.aspx

You will need to use the Userform's Initialize event to set the properties if they are going to vary. The Properties windows shows well, properties that you can set during design time or in events like Initialize.

onexc · Dec 10th 2016

Re: Popup Message Box with custom font color

Wow! KjBox! This is really what I want to have. Thank you very much! You are perfect as usual. Just a little question: Is it possible to set font size as bold and different size (if applicable)?

onexc · Dec 10th 2016

Re: Popup Message Box with custom font color

Hello Mr. Hobson,

I made userform before but not done with custom features with onclick event over a macro button. Sorry for this, I am still beginner for VBA, but I promise I'll be more experienced within next 6 months.

Quote from Kenneth Hobson;782138

Have you never made a Userform before? It would have a TextBox control with Multiline property set to True and a CommandButton control. There are many tutorials that explain how to do it. This link shows the steps to do what you want, sort of. https://msdn.microsoft.com/en-…rary/office/ff840656.aspx

You will need to use the Userform's Initialize event to set the properties if they are going to vary. The Properties windows shows well, properties that you can set during design time or in events like Initialize.

**KjBox** · Dec 10th 2016

Re: Popup Message Box with custom font color

You can change any of the properties for the "lblMessage" control by opening VB editor, select the "frmMessage" user form, then select the Label (it can be difficult to see because there is no caption text and the back color is the same as the user form back color). You can then change the required properties via the Properties window.

**KjBox** · Dec 10th 2016

Re: Popup Message Box with custom font color

Or, if you want different font size and boldness for each message you can set those Properties at run-time. Change the macro to

Code

Sub Message()


    With frmMessage
        If Application.Caller = "Button 1" Then
            With .lblMessage
                .Caption = "This is the message for Button 1."
                .Font.Bold = False
                .Font.Size = 12
            End With
        ElseIf Application.Caller = "Button 2" Then
            With .lblMessage
                .Caption = "This is the message for Button 2, this is a much longer message, but on one line."
                .Font.Bold = True
                .Font.Size = 10
            End With
        ElseIf Application.Caller = "Button 5" Then
            With .lblMessage
                .Caption = "This is the message for Button 3." & vbLf & "This will be on a second line"
                .Font.Bold = False
                .Font.Size = 9
            End With
        End If
        .Width = .lblMessage.Width + 30
        .cmdOK.Left = .Width - (.Width / 2) - 21
        .Show
    End With
    
End Sub

Display More

Change the Properties to suit.

**Kenneth Hobson** · Dec 10th 2016

Re: Popup Message Box with custom font color

No problem, questions is what the forum is for. There are basically two categories of "buttons", (1) Form and (2) ActiveX. Form controls will only have the Click event. Assign a macro and when the button is clicked, they execute. Form controls can be inserted via the Insert button on the Developer group as are ActiveX controls. Forms can be shapes. Insert a Shape and make it look like a button. The Form macros will reside in Modules. ActiveX controls on the sheet will be stored in the sheet they are on. ActiveX controls are also in the Toolbox from the VBE View menu that can be added to Userforms. It sounds like you know this.

I would recommend that you enable the Developer group. Right click the ribbon and customize. Select the Developer option. You can then more easily work with macros and controls.

onexc · Dec 10th 2016

Re: Popup Message Box with custom font color

Thank you very much Kjbox, you are the one... for your help and being kind. Your code works like a clock.

onexc · Dec 10th 2016

Re: Popup Message Box with custom font color

Thank you, Mr. Hobson for such useful information, I'll keep them in my notepad as saved. Also surfing through the web, Ozgrid at first, also reading some books.

**KjBox** · Dec 10th 2016

Re: Popup Message Box with custom font color

You're welcome

onexc · Dec 12th 2016

Re: Popup Message Box with custom font color

Hello KjBox,

May I ask a little question, too? When I applied bigger fonts with the way you suggested, it's resized to the text in width but not height; bigger texts move out of the message box and don't display completely. I tried to find out a way to autosize height but no success. is there a way to auto resize height and also cmdOK button to the bottom?

**KjBox** · Dec 12th 2016

Re: Popup Message Box with custom font color

What font size are you trying to get, and what is the message that needs to be displayed?

onexc · Dec 12th 2016

Re: Popup Message Box with custom font color

Quote from KjBox;782242

What font size are you trying to get, and what is the message that needs to be displayed?

Hello KjBox,

Size 16 and Bold. It was a sentence... though i don't remember whole of it. It's three lines used

Code

& vbLf &

for each one.

onexc · Dec 13th 2016

Re: Popup Message Box with custom font color

Hello KjBox,

Is there a something new with the issue, that's really important for me, sorry.

**KjBox** · Dec 13th 2016

Re: Popup Message Box with custom font color

Change the code to this, the user for height and command button position will adjust to suit the height of the message label.

Code

Sub Message()


    With frmMessage
        If Application.Caller = "Button 1" Then
            With .lblMessage
                .Caption = "This is the message for Button 1."
                .Font.Bold = False
                .Font.Size = 12
            End With
        ElseIf Application.Caller = "Button 2" Then
            With .lblMessage
                .Caption = "This is the message for Button 2, this is a much longer message, but on one line."
                .Font.Bold = True
                .Font.Size = 10
            End With
        ElseIf Application.Caller = "Button 5" Then
            With .lblMessage
                .Caption = "This is the message for Button 3." _
                        & vbLf & "This will be on a second line" _
                        & vbLf & "and now for another test this on a third line, and is a longer string of text."
                .Font.Bold = 1
                .Font.Size = 16
            End With
        End If
        .Height = .lblMessage.Height + 70
        .Width = .lblMessage.Width + 30
        With .cmdOK
            .Top = .Parent.lblMessage.Top + .Parent.lblMessage.Height + 15
            .Left = .Parent.Width - (.Parent.Width / 2) - 21
        End With
        .Show
    End With
    
End Sub

Display More

onexc · Dec 19th 2016

Re: Popup Message Box with custom font color

Hello KjBox

I apologize for replying after 5 days due to my off days. This code is what i needed to have. Thank you for your patient and help; you are the one as usual!

**KjBox** · Dec 19th 2016

Re: Popup Message Box with custom font color

You're welcome, and thank you for the added reputation!

onexc · Dec 22nd 2016

Re: Popup Message Box with custom font color

Hello KjBox,

May I ask very last question on this subject; i tried it myself for several days but no success: i discovered that if i use a listbox, i can change background color with no restriction unlikely standart message box colors. How can i use listboxes as if a message box? In addition, is there a trick to apply one line as bold and others as normal?

Thank you and I apologize for these extra two questions.

**KjBox** · Dec 22nd 2016

Re: Popup Message Box with custom font color

You could use a ListBox in a userform in stead of a Label, but I really do not see any advantage in that. I would continue to use a Label and use "vblf" to place text on a next line.

It is not possible to have rows in a ListBox formatted differently, all have to be formatted the same.

Hope that helps.

Popup Message Box with custom font color

Participate now!

Share