Message alert other than MsgBox in Excel?

Question

Is there another way to display messages in Excel other than the MsgBox?

I was thinking of the security alert/enable macros alert style. Can I use that same space to display some text?

I am trying to notify users without them needing to click a button.

I'm not sure how to access the yellow box on top but two alternatives 1. You could put the Alert is some cell within the activesheet. 2. You can put a msgbox and automatically hit "ok" so the user doesn't need to take any action. — izzymo, Apr 09 '15 at 07:58
Closely related: http://stackoverflow.com/questions/5459734/using-messagebox-without-pausing-the-application-vba — Jean-François Corbett, Apr 09 '15 at 08:06

MikeD · Accepted Answer · 2015-04-09T08:07:02.717

If you want to display messages without the user needing to interact, you can create a user form and display it modeless, meaning that after displaying the form the normal execution of your VBA continues.

example (form = "UserMsgBox", label = "Label1")

Sub Test()
    UserMsgBox.Show vbModeless

    UserMsgBox.Label1.Caption = "This is my 1st message to you"
    UserMsgBox.Repaint
    Application.Wait Now + TimeValue("00:00:02")

    UserMsgBox.Label1.Caption = "This is my 2nd message to you"
    UserMsgBox.Repaint
    Application.Wait Now + TimeValue("00:00:02")

    UserMsgBox.Label1.Caption = "This is my 3rd and last message to you"
    UserMsgBox.Repaint
    Application.Wait Now + TimeValue("00:00:02")

    UserMsgBox.Hide

End Sub

Secondly you can display text in the status bar area in the bottom of the Excel application window by using

Application.StatusBar = "My bottom line message to you"

If there is no way to access the Excel security/enable macro bar, then this is the way to go. Thanks. — Moritz Schmitz v. Hülst, Apr 09 '15 at 08:11

score 1 · Answer 2 · answered Apr 09 '15 at 08:15

1

I would rather go with OnTime to schedule a task rather than using Wait. Wait may prevent all other Excel activity. So, the user has to pretty much wait untill the form is gone. With OnTime the user still has the option to do something in the meantime. So, this is my proposition (also usering the Form approach):

Public Sub showform()

Load UserForm1
UserForm1.StartUpPosition = 0
UserForm1.Left = Application.Left + Application.Width * 0.9 - UserForm1.Width
UserForm1.Top = Application.Top + Application.Height * 0.9 - UserForm1.Height
UserForm1.Show (False)
Application.OnTime Now + TimeValue("0:00:05"), "closeform"

End Sub

Public Sub closeform()

UserForm1.Hide
Unload UserForm1

End Sub

answered Apr 09 '15 at 08:15

Ralph

9,284
4
32
42

How does this address the question? – Jean-François Corbett Apr 09 '15 at 08:18
sure you would ... my above was just a quick & dirty demo of a VBA advancing after a form being displayed ... the essential here actually is the use of `Repaint` after you changed the caption of `Label1` – MikeD Apr 09 '15 at 08:19
What I like in your approach is the absolute positioning of the form to an area where it is convenient. OTOH I recommend to use the predefined constant `vbModeless` instead of a generic `False` for the sake of clarity. – MikeD Apr 09 '15 at 08:30

score -1 · Answer 3 · answered Apr 09 '15 at 09:42

in answer to the Question please see below link for the list of all types of msgbox

Syntax for use also below - hope this helps!

MsgBox("Some Text", vbCritical, "Message title")

https://msdn.microsoft.com/en-us/library/139z2azd%28v=vs.90%29.aspx

Cheers

score -1 · Answer 4 · answered Apr 13 '17 at 02:51

-1

You can use this type of message box instead of the default VBA MsgBox. It supports Unicode

Application.Assistant.DoAlert "Hoc Excel Online Title", "your message", 0, 4, 0, 0, 0

answered Apr 13 '17 at 02:51

Đức Thanh Nguyễn

9,127
3
21
27

Message alert other than MsgBox in Excel?

4 Answers4