Calendar Macro

Hi Andrew,

You can use the following code in the VBA module of the worksheet which contains the cell from which you want to trigger the routine:

Option Explicit


Const strCalendarCell   As String = "A5"


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Cells.Count = 1 And Not Intersect(Target, Me.Range(strCalendarCell)) Is Nothing Then
        frmCalendar.Show
    End If

End Sub

The check of "Target.Cells.Count = 1" just ensures that the routine won't be triggered if the user selects a multi-cell range which includes cell A5.

Hope this helps - please let me know how you get on.

Best regards,

Greg M

I am confused... What is the full code to show calendar pop up when cell "A5" is selected?

Thanks

Hi again,

Excel triggers the "Worksheet_SelectionChange" event whenever a cell or range of cells is selected.

The "If" statement first checks that only a single cell is selected, and then checks that there is a valid intersection between the selected cell ("Target") and cell A5 ("strCalendarCell") - in this case, a "valid intersection" means that the selected cell and cell A5 are one and the same.

When the "If" statement returns a value of TRUE, the "frmCalendar.Show" statement is executed.

Regards,

Greg M

I tried the code and it doesn't seem to work. Please check attached file.

I would also like to activate when "B5" is active along with "A5".

Thanks for all your help!!!

Hi again,

You entered the code in a standard VBA module - you must enter it in the VBA module for the WORKSHEET. Right-click on the worksheet tab & select "View Code" - this will display the VBA module for the worksheet.

To extend the "activation range" use:

Const strCalendarCell   As String = "A5:B5"

Regards,

Greg M