LEARN MS-ACCESS TIPS AND TRICKS: WScript

Public Function ShortCut() Dim objwshShell As Object Dim objShortcut As Object Set objwshShell = VBA.CreateObject("WScript.Shell") Set objShortcut = objwshShell.CreateShortCut("C:\Users\User\Desktop\Hello.txt.lnk") With objShortcut .TargetPath = "C:\Windows\Notepad.exe " .Arguments = "D:\Docs\Hello.txt" .WorkingDirectory = "D:\Docs" .Description = "Opens Hello.txt in Notepad" .HotKey = "Ctrl+Alt+9" .IconLocation = "C:\Windows\System32\Shell32.dll,130" .WindowStyle = 2 .Save End With End Function

Option Compare Database Option Explicit Public Function DesktopShortCut(ByVal strShortCutName As String, _ ByVal strProgramPath As String, _ ByVal strFilePath As String, _ Optional strWorkDirectory As String = "", _ Optional ByVal strHotKey As String = "") As Boolean On Error GoTo DesktopShortCut_Err '----------------------------------------------------------------- 'Function: DesktopShortCut() 'Author: a.p.r. pillai 'Rights: All Rights(c) Reserved by www.msaccesstips.com 'Remarks: You may modify the Code, but need to keep these 'Rem lines intact. 'Parameters '----------------------------------------------------------------- '1. Shortcut Name: Shows below the Desktop Icon '2. strProgramPath: e.g.: "C:\Windows\System32\Notepad.exe" '3. strfilePath: File PathName to Open, e.g. "D:\Docs\Helloworld.txt" '4. Optional strWorkDirectory: e.g. "D:\Docs" '5. Optional strHotKey: Quick Launch - e.g. Ctl+Alt+9: 1-9,A-Z '----------------------------------------------------------------- Dim objwshShell As Object Dim objShortcut As Object Dim strPath As String Dim strProg As String, a As String, b As String Dim strTemp As String Dim DeskPath As String Dim strmsg As String Dim badchar As String, Flag As Boolean Dim j, count As Integer strPath = Environ("Path") 'Validation Checks GoSub IsValidName GoSub ValidateParams 'Find Current User Desktop strTemp = Mid(strPath, InStr(1, strPath, "C:\Users\"), 25) DeskPath = "C:\Users\" & Mid(strTemp, 10, InStr(10, strTemp, "\") - 10) & "\Desktop\" DeskPath = DeskPath & strShortCutName & ".Lnk" Set objwshShell = VBA.CreateObject("WScript.Shell") Set objShortcut = objwshShell.CreateShortCut(DeskPath) With objShortcut If InStr(1, Trim(strProgramPath), " ") > 0 Then .TargetPath = Chr(34) & Trim(strProgramPath) & Chr(34) '="C:\Windows\Notepad.exe" Else .TargetPath = Trim(strProgramPath) End If If InStr(1, Trim(strFilePath), " ") > 0 Then .Arguments = Chr(32) & Chr(34) & strFilePath & Chr(34) '="D:\Docs\Hello.txt" Else .Arguments = Chr(32) & strFilePath '="D:\Docs\Hello.txt" End If 'Optional Working Directory If Len(strWorkDirectory) > 0 Then .WorkingDirectory = strWorkDirectory '="D:\Docs" End If 'Optional Keyboard HotKey If Len(Nz(strHotKey, "")) > 0 Then .HotKey = "Ctrl+Alt+" & strHotKey '= "Ctrl+Alt+K" Else .HotKey = "" End If .IconLocation = "C:\Windows\System32\Shell32.dll,130" '0 - 305 .WindowStyle = 2 .Save End With DesktopShortCut = True DesktopShortCut_Exit: Exit Function IsValidName: Flag = True badchar = "\/:*?" & Chr(34) & "<>|" count = 0 For j = 1 To Len(strShortCutName) If InStr(1, badchar, Mid(strShortCutName, j, 1)) Then count = count + 1 End If Next Flag = IIf(count, False, True) If Not Flag Then MsgBox "Shortcut Name: " & strShortCutName & vbCr & vbCr _ & "Contains Invalid Characters." & vbCr & vbCr _ & "*** Program Aborted. ***", , "DeskShortCut()" DesktopShortCut = False Exit Function End If Return ValidateParams: strmsg = "" 'Program Path If Len(Nz(strProgramPath, "")) > 0 Then 'Check whether the Program exists in the given path If InStr(1, strProgramPath, Dir(strProgramPath)) = 0 Then strmsg = "Program Path: " & strProgramPath & " Invalid." End If Else strmsg = "Program Path: Not found!" End If 'File Path If Len(Nz(strFilePath, "")) > 0 Then If InStr(1, strFilePath, Dir(strFilePath)) = 0 Then If Len(strmsg) > 0 Then strmsg = strmsg & vbCr & "File Path: " & strFilePath & " Invalid." Else strmsg = "File Path: " & strFilePath & " Invalid." End If End If Else If Len(strmsg) > 0 Then strmsg = strmsg & vbCr & "File Path: Not found!" Else strmsg = "File Path: Not found!" End If End If If Len(strmsg) > 0 Then MsgBox strmsg, , "DeskShortCut()" DesktopShortCut = False Exit Function End If Return DesktopShortCut_Err: MsgBox Err & " : " & Err.Description, , "DesktopShortCut()" DesktopShortCut = False Resume DesktopShortCut_Exit End Function

Introduction.

The new Message Box in Microsoft Access is quite an interesting addition—one that many of us have wished for for a long time. Imagine a Message Box that closes automatically after displaying some useful information, without requiring any user action to continue code execution. Now, this is possible. We can create a self-closing Message Box that closes after a specified time.

Such a Message Box can even act as a progress indicator during lengthy processes. It can display updates at regular intervals to reassure an impatient user that the program is still running smoothly.

This feature is built on the Popup() method of the Windows Scripting Object.

It accepts all the parameters of the standard Access MsgBox function, except for the [helpfile] and [context] arguments. Additionally, it introduces a new optional parameter—the time value (in seconds). This value specifies how long the Message Box will remain visible on the screen.

If a time value is supplied (say, 5 seconds), the Message Box will close automatically when the time expires.
If the user clicks a button before the time runs out, it closes immediately.
If no time value is provided, the Message Box behaves exactly like the standard MsgBox, waiting for user input.

This makes it especially useful for scenarios where you want to display status updates, progress messages, or alerts about upcoming tasks without interrupting the flow of program execution.

MesgBox() is the New Name.

We have named our new function MesgBox()—notice the extra “e” after the “M” in the original Access MsgBox function. This small change makes it both easy to remember and convenient to type while writing code.

Just like the standard MsgBox, the new MesgBox() function accepts the parameters msgText, buttons, and title. All button options (such as vbOkOnly, vbOkCancel, etc.) and icon options (vbCritical, vbInformation, etc.) remain valid. You can also combine them to define both the icon and the default selected button, for example:


vbOkCancel + vbCritical + vbDefaultButton2

The difference is that MesgBox() introduces one additional parameter: the time value (in seconds). This parameter specifies how long the message box remains visible before it closes automatically. For example, passing a value of 5 will display the message box for 5 seconds, after which it disappears without waiting for user input.

The sample image of the new MesgBox() with n seconds duration is given below:

The MesgBox automatically closes exactly after the specified number of seconds. The countdown value itself is not displayed in the message box.

If you omit the optional buttons parameter, the default OK button will still appear.

Note: If you want the user to be aware of how long the message will remain visible, you can concatenate the time value into the message text itself.

If the user clicks one of the displayed buttons before the full delay elapses, the message box closes immediately and does not wait for the specified duration. The time parameter is optional—if it is omitted or set to zero, MesgBox behaves just like the standard Access MsgBox, requiring the user to click a button to dismiss it.

Access MsgBox() and New MesgBox() Functions.

The new MesgBox() function requires only a minimal amount of code—just three lines. Before diving into the complete function code, let us first compare the syntax and behavior of the standard Access MsgBox() with the new MesgBox() function.

1. Access MsgBox()

The message box remains visible until the user clicks one of the displayed buttons.
Code execution is paused until the user responds.

Syntax:


opt = MsgBox(msgTxt, [Buttons] + [Icon] + [DefaultButton], [Title])

Example:


opt = MsgBox("Preparing Report, Please Wait...", vbOkCancel + vbInformation + vbDefaultButton2, "Reports")

2. New MesgBox()

The message box closes automatically after the specified number of seconds, or immediately if the user clicks one of the displayed buttons first.
Code execution continues after the delay (or instantly upon a button click).
The delay time is an integer, expressed in seconds, and passed as the second parameter.

Syntax:


opt = MesgBox(msgTxt, [intSeconds], [Buttons] + [Icon] + [DefaultButton], [Title])

Example:


opt = MesgBox("Preparing Report, Please Wait...", 5, vbOkCancel + vbInformation + vbDefaultButton2, "Reports")

In the new MesgBox() function:

msgText is the required first parameter.
The time delay (in seconds) is the second parameter.
The Buttons + Icon + DefaultButton combination is the third parameter.
The Title is the final parameter.

All parameters are optional except the first one, just like the standard Access MsgBox().

When the MesgBox function runs, the message box appears with the Cancel button preselected by default. If the user accepts the default option, they can simply press Enter to dismiss it or click on any other button of their choice. Otherwise, the message box will close automatically after 5 seconds.

Omitting the Time Param works like Access MsgBox().

If selecting an option is mandatory, simply omit the Time Value parameter or set it to 0 (zero). In this case, the MesgBox will remain on the screen until the user selects one of the displayed option buttons.

Note: The new MesgBox() function is built on the Microsoft Windows Script Host’s Popup() method. It supports all the parameter values of the Access MsgBox function (except the HelpFile and Context parameters), but in a slightly different order. Refer to the Popup() method syntax shown below:'Syntax:

expression = winShell.Popup(strText, [intSeconds], [strTitle], [intButtons])

Since we have derived our new MesgBox Function from Windows Script Host Object’s Popup() method, we have organized the order of parameters for our function in almost the same order as the Access MsgBox() function. But, they will be passed to the Popup() function in the required order.

VBA Code for the New MesgBox() Function

The entire implementation requires only three lines of code, making it both simple and efficient. This custom function leverages the Windows Script Host’s Popup() method to extend the functionality of the standard Access MsgBox.

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal intSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, intSeconds, TitleText, intButtons)

End Function

If the user clicks on one of the displayed buttons, the function returns the corresponding Integer value (same as the Access MsgBox function), which your calling program can capture and evaluate.

You can also experiment with different parameter combinations directly from the Immediate Window (Debug Window) to quickly test its behavior and familiarize yourself with its usage. For example:


? MesgBox("Process Completed Successfully!", 3, vbOKOnly + vbInformation, "Done")

This will display a message box with an OK button and an information icon, which will automatically close after 3 seconds.


? MesgBox("Do you want to continue?", 0, vbYesNo + vbQuestion + vbDefaultButton2, "Confirm")

This version will behave like the standard MsgBox, remaining on screen until the user clicks Yes or No.

The MesgBox() Function Demo Test Subroutine.

We have a demo program to test the new MesgBox() function with different sets of Buttons, Icons, and time values. When the user clicks on a Button or is allowed to close it, then the program checks the returned value and displays a second MesgBox with an appropriate response and disappears after 3 seconds.

Public Sub MesgBox_example()
Dim opt As Integer
Dim Title As String
Dim intSeconds As Integer
Dim optSeconds As Integer
Dim Tip1 As String

Title = "MesgBox_example"
intSeconds = 5
optSeconds = 3
Tip1 = "Click Button before time ends in " & intSeconds & " Seconds" & vbCr & vbCr

'//Enable only one of the four methods given below.

opt = MesgBox("Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation, "Info")

'opt = MesgBox(Tip1 & "Preparing Monthly Report" & vbCr & "Please wait . . .", intSeconds, vbInformation + vbOKCancel, "Info")

'opt = MesgBox("Cannot Delete Records . . .!", , vbExclamation + vbAbortRetryIgnore + vbDefaultButton3, "Delete")
    
'opt = MesgBox("Database Shutdown . . .?", , vbCritical + vbYesNo + vbDefaultButton2, "Shutdown")

'// Test whether the button clicked or not, if it did then which button/returned value.
Select Case opt
    Case -1 ' No button selected, MesgBox closed automatically after the time specified.
    
        '//In this MesgBox the time parameter is omitted, works like Access MsgBox. Need to click the Button to close.
        MesgBox "No Button Selected." & vbCr & "Click Ok button here to close this MesgBox.", , vbInformation, Title
    
'//The following options work only when the MesgBox button receives the Click.
    Case vbOK '- 1
        MesgBox "Preparing Report" & vbCr & "User's Response Ok", optSeconds, vbInformation, Title
    
    Case vbCancel '- 2
        MesgBox "Not to Prepare Report" & vbCr & "User's Response Cancel", optSeconds, vbInformation, Title
    
    Case vbAbort '- 3
        MesgBox "Record Deletion Aborted.", optSeconds, vbExclamation, Title
    
    Case vbRetry '- 4
        MesgBox "Retrying Record Deletion.", optSeconds, vbExclamation, Title
    
    Case vbIgnore '- 5
        MesgBox "Record Deletion Process Ignored.", optSeconds, vbExclamation, Title
    
    Case vbYes '-  6
        MesgBox "Yes, Shutdown Approved.", optSeconds, vbCritical, Title
        'DoCmd.CloseDatabase
    Case vbNo '- 7
        MesgBox "Database Shutdown Denied.", optSeconds, vbCritical, Title
End Select


End Sub

Save the Code in the Access Global Module.

Copy both the MesgBox() and MesgBox_Example() procedures into a Global Module and save the code. Then, compile the project to ensure everything is in order.

In the MesgBox_Example() routine, the first MesgBox() call is already enabled. Place the cursor anywhere inside the code and press F5 to run it. This will display a message box with only the OK button. After 5 seconds, the MesgBox will close automatically, and the returned value (Opt variable) will be –1.

The returned value is then evaluated in the subsequent Select Case block to display an appropriate follow-up message. In this case, the time parameter is omitted, so the MesgBox behaves like the standard Access MsgBox, requiring the user to click a button to close it.

Run the same option a second time. This time, click the OK button before the timeout expires. The button click will return the value 1 to the Opt variable, and the follow-up message will be displayed for 3 seconds.

To experiment further, comment out the tested line and enable the next one by removing the comment symbol. You can repeat this method with different button combinations and icons to see how the MesgBox responds in each scenario.

A Critical Message Box:

The MesgBox() function is called directly from the Debug Window (Immediate Window) with the function parameters as given below:

msgTxt = "Database Shutdown . . .?"

MesgBox msgTxt,,vbYesNo+vbCritical+vbDefaultButton2,"Shutdown"

Points to Note.

Take Note of these Side effects:
Since the MesgBox() function is based on the Windows Script Host Popup() method, it behaves differently from the standard Access MsgBox:
- Even if the Access application window is minimized, the MesgBox will still appear on the Windows Desktop.
- If the time parameter is omitted, the user must click a button (like in Access MsgBox) to dismiss the MesgBox.
- If the user clicks anywhere outside the MesgBox, it will move behind the Access application window and remain visible on the Windows Desktop.
- While the MesgBox is active, your VBA program execution is paused, waiting for the user’s response. If you try to close the database during this time, the attempt will be ignored without warning. To forcefully close Access, you must use Exit Access from the Office Button.
We are already familiar with Microsoft Windows Common Controls such as TreeView, ListView, ImageList, and others, which depend on the MSCOMCTL.OCX library. The MesgBox() function, however, relies on the Windows Script Host Object Model (wshom.ocx) and does not require manually adding a reference in the VBA editor.
If you encounter any issues using this feature, check the official Microsoft Support pages for troubleshooting guidance.
MesgBox() Function with Error Handling

Here is the improved version of the MesgBox() function with error-trap lines included:

Public Function MesgBox(ByVal msgText As String, _
    Optional ByVal TimeInSeconds As Integer, _
    Optional ByVal intButtons = vbDefaultButton1, _
    Optional TitleText As String = "WScript") As Integer

On Error GoTo MesgBox_Err
Dim winShell As Object

Set winShell = CreateObject("WScript.Shell")

MesgBox = winShell.PopUp(msgText, TimeInSeconds, TitleText, intButtons)

MesgBox_Exit:
Exit Function

MesgBox_Err:
winShell.PopUp Err & " : " & Err.Description, 0, "MesgBox()", vbCritical
Resume MesgBox_Exit
End Function

Now that we have compared the advantages and disadvantages of both MsgBox and MesgBox, the key takeaway is: use them sparingly and wisely.

If you plan to distribute your application with the new MesgBox() function, always test it on the target system to confirm that it works as expected in the new environment.