VBA & Macro Problems

Create an Excel Calendar with a VBA Pop-Up Information Form

Rana GarciaRana Garcia Nov 12, 2026 870 views

Question details

The user wants an Excel calendar that opens a custom information form when a calendar cell is selected.

Create an Excel Calendar with a VBA Pop-Up Information Form
Product
Microsoft Excel
Device & OS
not provided
Scenario
A calendar worksheet needs to collect scheduling information such as an activity and client name through a pop-up data-entry form.
Observed behavior
Excel has no built-in feature that automatically opens a custom data-entry pop-up when a calendar cell is clicked.
Before you start

Decide which worksheet cells represent calendar dates and define the fields the form must collect, such as activity, client name, start time, and notes. Also confirm that macros are permitted in the workbook and save the file as an Excel Macro-Enabled Workbook (*.xlsm).

Solution 1Recommended

Build the calendar pop-up with a VBA UserForm

Use this approach when the calendar must open a custom form and write the submitted information back to the selected date.

Excel does not provide a built-in calendar-cell pop-up form. A VBA worksheet event can detect the selected date, open a UserForm, and use the selected cell as the destination for the submitted details.

The exact VBA controls and output layout depend on the fields required by the schedule. Test the form with a copy of the workbook before distributing a macro-enabled file.

1
Open the VBA editor

Open the workbook in Excel, press Alt+F11, and choose Insert > UserForm in the Visual Basic Editor. In the Properties window, name the form CalendarEntryForm.

2
Add the form fields

Use the Toolbox to add TextBox controls for the activity, client name, and notes, plus a CommandButton for saving. Rename the controls to ActivityTextBox, ClientTextBox, NotesTextBox, and SaveButton in the Properties window.

3
Store the selected calendar cell

In the worksheet's code module, double-click the calendar sheet in the Project Explorer and add a Worksheet_SelectionChange event. Restrict the event to the calendar range, such as B3:H8, then assign the selected cell to a public worksheet or module variable before running CalendarEntryForm.Show.

4
Open the form when a date is selected

In the calendar worksheet module, use a condition such as `If Not Intersect(Target, Range("B3:H8")) Is Nothing Then` and call `CalendarEntryForm.Show`. Replace B3:H8 with the actual date-cell range.

5
Write the submitted data to the calendar

In the SaveButton_Click procedure, validate that ActivityTextBox and ClientTextBox are not empty, write the values to the selected date's linked schedule area or a separate appointments table, then run `Unload Me`. Use Developer > Design Mode and test several calendar cells after enabling macros.

Macro security matters: Users must enable macros for the click event and UserForm to work. If the workbook will be shared, confirm that its security policy allows signed or trusted VBA projects.
Free Microsoft Office alternative

Create and manage calendar worksheets in WPS Office

WPS Office is a free, lightweight Microsoft Office alternative for building calendar worksheets, entering scheduling information, and migrating familiar Excel files. It supports common .xlsx and .xls formats, offers a familiar spreadsheet interface, and can help you manage the calendar layout while custom macro requirements are evaluated.

  1. 1. Open or create the calendar: Launch WPS Office, open WPS Spreadsheets, and choose a calendar template or create a new spreadsheet from the Home screen.
  2. 2. Build the date layout: Use the Insert and Home tabs to add date headings, format calendar cells, and create a separate appointments table for activity and client details.
  3. 3. Import an Excel workbook: Choose Menu > Open, select the .xlsx or .xls workbook, and review the calendar layout and data after it opens in WPS Spreadsheets.
Create calendar layouts and enter activity, client, and scheduling details in a familiar spreadsheet interface.Lightweight free office suite for planning worksheets and everyday spreadsheet work.Supports Microsoft Excel .xlsx and legacy .xls file formats for easier migration.Use familiar formatting, tables, formulas, and worksheet tools when rebuilding a calendar template.
microsoft office alternative - wps office

Frequently Asked Questions

Can Excel open a custom form automatically when I click a calendar date?

Not with a standard worksheet feature. Use a Worksheet_SelectionChange VBA event to detect a selected date cell and call a VBA UserForm.

What controls should an Excel calendar pop-up form include?

Common controls include text boxes for the activity and client name, a date or time field, a notes box, and a Save button. Add validation for required fields before writing the data.

Where should the form save the appointment information?

It can write details to a linked row or column beside the calendar, or append each appointment to a separate table with columns for date, activity, client, time, and notes.

Why does the VBA calendar form not appear after I click a date?

Check that the workbook is saved as .xlsm, macros are enabled, the event code is in the calendar worksheet module, and the selected cell falls inside the range checked by Worksheet_SelectionChange.

Can WPS Office replace Excel for this calendar project?

WPS Office can create and edit compatible spreadsheet calendars and supports common .xlsx and .xls files. Compatibility with custom Excel VBA forms may vary by WPS version and platform, so test any macro-enabled workbook before relying on it.