VBA & Macro Problems

How to Create an Excel VBA Message Box for Staff Leave Conflicts

Maira Mehtab Sep 28, 2026 870 views

Question details

The user needs an Excel macro to trigger a message box that alerts them with specific staff leave details when a conflicting travel date is entered.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Entering travel dates into a schedule and needing real-time notifications if the dates conflict with existing holidays or staff leave records.
Observed behavior: Standard Data Validation can restrict invalid entries but cannot pull dynamic staff names and leave details into the alert, requiring a custom VBA solution.

Before you start

Ensure your workbook is saved as an Excel Macro-Enabled Workbook (.xlsm) and that you have structured your Holidays and Staff Leave records into defined tables or named ranges.

Solution 1Recommended

Use a Worksheet_Change Event VBA Procedure

This custom VBA script monitors cell changes and cross-references entered dates against leave tables to build a detailed conflict message containing staff names.

A Worksheet_Change event automatically runs your VBA code whenever a specific cell value is updated. This is the optimal approach when your error message needs to dynamically display contextual data, such as a staff member's name or exact leave dates.

Open the VBA Editor

Press Alt + F11 to open the Microsoft Visual Basic for Applications editor in Excel.

Access the Worksheet Module

In the Project Explorer pane on the left, double-click the name of the worksheet where you enter the travel dates.

Create the Change Event

At the top of the code window, select 'Worksheet' from the left dropdown and 'Change' from the right dropdown to generate a Private Sub Worksheet_Change(ByVal Target As Range) block.

Ignore Deletions and Undos

Immediately inside the macro, type 'If Target.Value = "" Then Exit Sub'. This prevents the message box from accidentally triggering when you delete a date or undo an action.

Add the Conflict Logic

Write loops or use the Find method to compare the Target.Value against your Holiday and Staff Leave tables. If a match is found, append the staff details to a string variable and display it using 'MsgBox conflictString, vbExclamation, "Scheduling Conflict"'.

Target Intersect: To optimize performance, wrap your main code inside 'If Not Intersect(Target, Range("YourTravelDates")) Is Nothing Then' so the macro only runs when specific cells are modified.

Solution 2

Use Data Validation with MATCH and COUNTIFS

If you are using an older version of Excel like Excel 2013 and do not strictly need detailed staff names in the error message, Data Validation offers a macro-free alternative.

Advanced Macro Capabilities

Manage Complex VBA Scripts Smoothly in WPS Spreadsheet

WPS Spreadsheet provides powerful built-in VBA support, allowing you to run custom Worksheet_Change events and complex conflict-checking macros effortlessly.

1. Open your Workbook: Launch WPS Spreadsheet and open your .xlsm scheduling file.
2. Access the Developer Tab: Navigate to the Developer tab located on the top ribbon menu.
3. Launch the VBA Editor: Click the 'VBA Editor' button to view and manage your macro code.
4. Test the Alert: Enter a date into the worksheet to trigger the conflict message box natively within WPS.

Fully compatible with Microsoft Excel (.xlsm) formats and macro scriptsIntegrated VBA editor to seamlessly create and modify Worksheet_Change eventsFree and lightweight alternative for powerful spreadsheet automationCross-reference holiday schedules and data validations flawlessly

microsoft office alternative - wps office

Frequently Asked Questions

Why does my VBA message box trigger when I delete a date?

Deleting a cell's content qualifies as a cell change, which triggers the Worksheet_Change event. To fix this, add the code 'If Target.Value = "" Then Exit Sub' at the very beginning of your procedure so it ignores blank inputs.

Can Data Validation display a specific staff member's name in the error alert?

No. Excel's standard Data Validation feature relies on static text for its error messages. It cannot dynamically reference cell values like a staff member's name. To show specific text based on the matched conflict, a VBA macro is required.

Why doesn't the XMATCH function work for finding overlaps in Excel 2013?

The XMATCH function is only available in newer versions of Excel (Microsoft 365 and Excel 2021+). If you are using Excel 2013, you must rely on standard functions like MATCH, INDEX, or COUNTIFS for data validation and array formulas.

Excel & Data Problems