2

I would like to create/use toasters notifications on Excel. Because we already use MsgBox to notify the user that something happen.

But it make the script to stop (pause).

Have you any idea of how to do ? On google there is "System Tray Notification" but it need a lot of code and this is a old method. Can't find if there is a new method.

For example, the plugin from SAP : "Analysis For Office" put notifications on Excel.

enter image description here

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user3114471
  • 155
  • 2
  • 11
  • 1
    Looks like you need to use some custom DLL files, you could build a pretty bad version with UserForms but it wouldn't be as good. [Here's](https://stackoverflow.com/questions/39224308/non-blocking-toast-like-notifications-for-microsoft-access-vba) a previous post about a similar topic – tomBob Nov 15 '19 at 14:48
  • 1
    If all you want to do is display a `Msgbox`, can you not just trigger a `VBScript` to do that while excel code continues to run? – Zac Nov 15 '19 at 15:01
  • 1
    You could have a userform show in vbModeless state and hide it once the code is done. No external libs needed. – jkpieterse Nov 15 '19 at 15:23
  • Thanks, I tried msgbox and userform with modeless and "wait". But it make the screen block. So it's pratically that, just having the timer wait in background. (Modeless macro so) – user3114471 Nov 15 '19 at 16:02

2 Answers2

2

Using the SAP Analysis For Office Excel plugin, you can define messages and add them to the standard SAP-AnalysisForOffice message dialog, by using SAPAddMessage:

Dim lResult As Long

lResult= Application.Run("SAPAddMessage", "This is a new error message!", "ERROR")

The message 'This is a new error message' with severity Error is displayed in the message dialog.

It will do the same as the picture sent with my question.

References:

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
user3114471
  • 155
  • 2
  • 11
1

I had a similar requirement (mainly for debugging)

My solution was to pop up a small form with a single label control and unload it 4 seconds later. The form proeprties are set no not show modal etc.

in a VBA "Module"

Private mFrmToast As frmToast
Public Sub clearToast()
    On Error Resume Next
    If Not mFrmToast Is Nothing Then


        mFrmToast.Hide
        Unload mFrmToast

        Set mFrmToast = Nothing

    End If
End Sub
Public Sub showToast(message As String)
    On Error GoTo er_clear_in_4
    If mFrmToast Is Nothing Then
        Set mFrmToast = New frmToast
    End If
    mFrmToast.message = message
    If Not mFrmToast.Visible Then
        Call mFrmToast.Show(False)
    End If
    er_clear_in_4:
    Application.OnTime Now + TimeValue("00:00:04"), "clearToast"

End Sub

The form "code behind" module contained a sample write only property, "message".

Option Explicit

Public Property Let message(ByVal sMessage As String)
  lblMessage.Caption = sMessage
End Property

The usage is simply showToast("your message here")

Darren Gill
  • 29
  • 1