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

feetwet
  • 2,914
  • 7
  • 41
  • 80
user3114471
  • 145
  • 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

3 Answers3

4

I looked into this once and almost gave up on it entirely until I found a rather 'cheaty' way of alerting users of info without suspending execution, that works on my users machines.

We run Windows 10 here and have SCCM installed for software distribution and updates. I've absolutely no idea if that's mandatory in Windows for updates or not, so I've no idea if this works for you.. but the following code works a treat here if you don't mind the notification resembling a Software Centre notification:

Sub Toastnote(ccmTitle, ccmText)
    Shell "c:\windows\ccm\sctoastnotification.exe """ & ccmTitle & """ """ & ccmText & """ "
End Sub

You can call it with:

toastnote "title goes here","message goes here"

It creates a little pop-up that looks like this:

enter image description here

As I say, it's a bit of a cheat and might confuse users who regularly receive CCM notifications, but for my user-base that wasn't an issue.

Lastly, it's probably worth wrapping this in an IF statement that checks the .exe file exists - just a thought..

CLR
  • 8,665
  • 1
  • 9
  • 22
1

Using the Plugin "Analysis For Office" from SAP, you can define messages and add them to the standard SAP-AnalysisForOffice message dialog :

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.

Source

Details about SAPAddMessage

user3114471
  • 145
  • 2
  • 11
0

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
  • 9
  • 1