non-blocking "toast" like notifications for Microsoft Access (VBA)

Question

How to show non-blocking "toast" like notifications in Microsoft Access? That does have some sort of animation and should not block the host application!

Trapshooting

If there are others like me who didn't know "toast": codeseven.github.io/toastr/demo.html – Beeck

Answer 1

My friend asked me about non-blocking toast like notifications for ms access. My first thought was, check google you will find plenty of samples. He wasn't happy with the samples he got.

He wanted something like (JQuery) non-blocking notifications. Something that user needs to know but not necessarily needs to interact.

Since threading is not possible in VBA, I thought, what if you could write your own .dll? so I ended up writing a .NET DLL which can be accessed by (windows) VBA code and show a toast notification. (the actual dll creation and accessing .NET dll from vba is another topic that I will cover later)(You can read more in my blog leave comments or suggestions as per your wish.)

For now, you can download the DLL that I've created from here: HERE

^{Edit: The above download links and the GitHub link have been updated to working links that I think belong to the author.}

If you are concerned about downloading unknown DLLs: VirusTotal Scan report

Add the DLL to your application's root folder and add following codes to your application.

'Module level public variable

Public gTOASTER As Object

' to save window metrics
Public Type RECT
    Left        As Long  ' x1
    Top         As Long  ' y1
    Right       As Long  ' x2
    Bottom      As Long  ' y2
End Type

#If VBA7 Then 
    Public Declare PtrSafe Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal lpLibFileName As String) As LongPtr
    Public Declare PtrSafe Function KRISH_VBA_TOOLS Lib "VBA_TOOLS.dll" () As Object
    Public Declare PtrSafe Function GetWindowRect Lib "user32" (ByVal hWnd As LongPtr, ByRef lpRect As RECT) As LongPtr 
#Else
    Public Declare Function LoadLibrary Lib "kernel32" Alias "LoadLibraryA" (ByVal strFilePath As String) As Long
    Public Declare Function KRISH_VBA_TOOLS Lib "VBA_TOOLS.dll" () As Object
    Public Declare Function GetWindowRect Lib "user32" (ByVal hWnd As LongPtr, ByRef lpRect As RECT) As LongPtr
#End If

Public Function FN_TOAST_DLL(iMessage As String, Optional iCLOSE_DURATION As Long = 3000, Optional iType As String = "success", Optional iANIME_DURATION As Long = 1000, Optional iFONT_COLOR As String = "#FFFFFF", Optional iX As Long = 0, Optional iY As Long = 0, Optional iANIME_DIRECTION As Integer = 1, Optional iPARENT_HWND As Long = 0)

On Error GoTo LABEL_EXIT_ROUTINE:

    If gTOASTER Is Nothing Then
        LoadLibrary (FN_APP_GET_BASE_PATH & "VBA_TOOLS.dll")
        Set gTOASTER = KRISH_VBA_TOOLS()
        GoTo LABEL_TOAST
    Else
        GoTo LABEL_TOAST
    End If

    On Error GoTo 0
    Exit Function

LABEL_EXIT_ROUTINE:
    msgbox iMessage & vbnewline & err.description
    Exit Function

LABEL_TOAST:
    'set background color. (pass any html color code)
    Select Case iType
        Case "error"
            iType = "#F76160"
        Case "success"
            iType = "#26ad82"
        Case Else
            iType = "#26ad82"
    End Select

    'if parent object is provided show the toast on top of the parent. if custom x, y is provided use x,y coordinated. if none provided use access app's locaiton.
    Dim mRect As RECT
    If iPARENT_HWND <= 0 Then
        If iX = 0 And iY = 0 Then
            GetWindowRect Application.hWndAccessApp, mRect

            iANIME_DIRECTION = 0 'anim direction 0 to down and 1 to up
        End If
    Else ' iPARENT_HWND > 0 Then 'parent_hwnd is null
        GetWindowRect iPARENT_HWND, mRect
    End If

    'set up some offsets
    iX = mRect.Left + 360
    iY = mRect.Top + 1


    On Error Resume Next
    gTOASTER.FN_SHOW_TOAST iMessage, iCLOSE_DURATION, iType, iANIME_DURATION, iFONT_COLOR, iX, iY, iANIME_DIRECTION

End Function

Public Function FN_APP_GET_BASE_PATH()
    Dim FN As String
    FN = Application.CurrentProject.path
    If VBA.Right(Application.CurrentProject.path, 1) <> "\" Then FN = FN & "\"
    FN_APP_GET_BASE_PATH = FN
End Function

parameter list from the DLL if you want to customise the fn_toast_dll function:

'    /// <summary>
'    ///
'    /// </summary>
'    /// <param name="iMessage">Message to display</param>
'    /// <param name="iDuration">Duration in Milliseconds to keep the toast before fading out..</param>
'    /// <param name="iBG_COLOR">HTML color code for your toast background...</param>
'    /// <param name="iANIME_DURATION">Millisecond value used to for fading in and out the Toast.. 1/4 is used to fade in rest to fade out..</param>
'    /// <param name="iFONT_COLOR">HTML Color code for the font..</param>
'    /// <param name="iX">x position on the screen. where the toast should appear</param>
'    /// <param name="iY">y position on the screen where the toast should appear</param>
'    /// <param name="iANIM_DIRECTION">{0,1} 0 will show/add further notifications downwards and 1 upwards.</param>
'    /// <returns></returns>

to show a notification call this method:

FN_TOAST_DLL "hello this is a green test" ' By default a success message with 3 seconds will be "toasted"
FN_TOAST_DLL "hello this is an error", 15000, "error"

Usage:

You can use this for any non interacting alerts.. like login success, or action cancelled alerts or anything that user does not need to press OK to acknowledge your message.

Goal I will upload the Dll project on GitHub and ask for contribution from other VBA C# experts to make it bit more fancier and available for all VBA developers.

Here is my GitHub link: GitHub Please contribute as much as you want and make this available for everyone :) I'll be happy if you can leave the main class name as it is.

A sample:

Trapshooting

Nice! If you can publish the DLL source code, that would be helpful. And perhaps add to the code in your answer a function header that describes the parameters (e.g. I'm not sure what iANIME_DIRECTION does). Thanks. – Beeck

@Beeck i've added the list of parameters. I will upload the DLL to GitHub and provide the link in the next post when I cover "how to access .NET dll form vba" – Trapshooting

That's very good thing, thanks! Looks like it doesn't work under 64 bit office. Could you please make 64 bit version of DLL? Also it would be great to add one more option - do not close the toast by timer if mouse pointer is above the toast. – Doordie

@SergeyS. 64bit Dll added can you test that? also mouse events added and the github link to my project is published. – Trapshooting

Thanks for quick update, it appears now under 64, but in a little bit different way: if you open second toast before first one disappeared, second toast appears, but first one disappears immediately, so on screen visible only one toast. – Doordie

@SergeyS. are you sure? i just tested on 64bit office and I don't have that effect. Anyway I've made some tweaks try to re-download the x64.dll and let me know – Trapshooting

@SergeyS. Glad if it works. Upvote if you liked it :)😉 – Trapshooting

@krishKM I'm curious if you're still around, supporting the DLL? With a decompiler I can see there's a lot of functionality not being utilized that's I'm not sure how to make use of. The primary feature that I (and likely others) would be to run code when a notification is clicked (perhaps using WM_LBUTTONDOWN?) ... in my case, for example, I'd want to open a web page, the URL depending on which notification was clicked. – Selfexplanatory

@Selfexplanatory Yes I'm still around. Yes that can be done. Although I accidentally deleted the project page. I will try to re-upload it again. – Trapshooting

@krishKM excellent - I've been playing with the notifications for hours. I'm going to use it along with an Excel workbook I have polling and analyzing an API, so I can be notified of new data without having to keep the workbook open, and it would be handy if I could click a notification and open a relevant web page – Selfexplanatory

@krishKM - accidentally deleted, hmmm I guess that means I fixed your broken link incorrectly? – Selfexplanatory

@Selfexplanatory yes that's different project. I will re-upload the DLL project again. I had two version of the dll one with my own signalR notificication server with passwords in the code. My VS got confused and published the personal one to the github. In the process of deleting that project I ended up deleting everything. – Trapshooting

@Selfexplanatory hyperlink enabled. checkout the sample in github page – Trapshooting

Hi @krishKM do you know if this will work in Outlook VBA? If I just add this to a module I get "Object don't support this property or method" errors on Application.CurrentProject.Path and GetWindowRect and even if I work around them with hard-coded values I get the same error on gTOASTER.FN_SHOW. LoadLibrary succeeds and gTOASTER seems to initialized – Dameron

@MiserableVariable yes it should work in all VBA environment. I assume for outlook, you need to strip off/replace any functions that comes form MS access. I.e. NZ() function, Form as argument, and you may need to add microsoft.scripting.runtime as reference. I tested it now and it worked. Also check my github page for latest version. – Trapshooting

Answer 2

Not sure if this deserves another answer! Please forgive if it hasn't! To answer "If i could publish the DLL source-code" and to make it easier to read/stand out for those who wants to contribute/participate to the DLL project.

I've added the DLL project to the GitHub and I will be adding updating new functions. If you would like to contribute please do. The project is written is C# to prove this concept hence code can be very messy. (Initial stage)

Please do improve: 

1. New animations
2. Perhaps sending messages back to host applications?
3. Better x,y position handling ?

and whatever you come up with.

I'll be happy if you could leave the main class name as it is "KRISH_VBA_TOOLS".

Here is the GitHub link:
https://github.com/krishKM/VBA_TOOLS

Enjoy.

Trapshooting

I'm sure there are plenty of people who would help if you uploaded the source code. You only have binaries published on your github page. – Slacks

Answer 3

My solution to this problem is to use the built in Windows 10 toast notification API. I call upon PowerShell where we can leverage BurntToast. My example is non-blocking because it returns right after calling PowerShell so your application can continue while the module is loaded. Even if PowerShell takes a minute to load the user will not notice. It fires fairly quickly in my testing.

First make a Pop-Toast.ps1 file that looks like this:

## Pop-Toast 
#Requires -Version 5
Param (
    [String]$cmdText="Test",
    [String]$cmdTitle="Example",
    [String]$cmdLogo="$PSScriptRoot\YourLogo.ico",
    [String]$thisApplication,
    [String]$appID
)



## Ensure we have the latest version of our module installed
$toastModule = "BurntToast"
if (Get-Module -ListAvailable -Name $toastModule) {
    # If you uncomment the next line it will update but each call is much slower.
    #Update-Module -Name $toastModule
} 
else {
    try {
        Install-Module -Name $toastModule -AllowClobber -Confirm:$False -Force  
    }
    catch [Exception] {
        $_.message 
        exit
    }
}

## Pop that tart!
$header = New-BTHeader -Id $appID -Title $thisApplication
New-BurntToastNotification -AppLogo $cmdLogo -Header $header -AppId $appID -Text $cmdTitle, $cmdText 

Then we make a complimentary VBA procedure that calls our PowerShell script:

Const PopToastPath As String = "Toast\Pop-Toast.ps1"

Public Sub PopToaster(ByVal toastText As String, Optional ByVal toastTitle As String, Optional ByVal toastLogoPath As String)
    Dim PSOptions As String
    PSOptions = " -WindowStyle hidden -ExecutionPolicy bypass -NonInteractive"
    
    Dim PSCommand As String
    PSCommand = "powershell.exe" & PSOptions & " -File " & PopToastPath & " -cmdText """ & toastText & """"
    If Not Trim$(toastTitle) = vbNullString Then PSCommand = PSCommand & " -cmdTitle """ & toastTitle & """"
    If Not Trim$(toastLogoPath) = vbNullString Then PSCommand = PSCommand & """ -cmdLogo """ & toastLogoPath & """"

    With CreateObject("Wscript.Shell")
        .Run PSCommand, 0, False
    End With
End Sub

NOTE: You can configure your application name and appID to make the toast appear to come from your own application. If you do not then it will be seen as coming from PowerShell.

Slacks