Popup Calendar

You can make the Calendar 'popup' where you want it to, by adjusting the 'Left' and 'Top' adjustments in the Code. For example, to make the Calendar display closer to the bottom of the Cell vertically, you can adjust the 'TopAdjustment', setting it to 0. You can use the following Code when you call the Calendar.Popup() Subroutine like this:

Sub PopUpCalendar_Positioning_Example()

    Call Calendar.Init(FadeIn:=True, _
                       FadeOut:=True, _
                       FadeSpeed:=6, _
                       FormatActiveCell:="dd/mm/yyyy")

    Call Calendar.Popup(LeftAdjustment:=-60, _
                        TopAdjustment:=0, _
                        DismissByEscape:=True)

End Sub
                            

The Calendar positioning will stick if the Window of Excel is adjusted, Freeze Panes are used or if the Worksheet is Zoomed (depending on how you set the 'Left' and 'Top' Parameters, there may be slight difference when Zoomed, but I thik this is acceptable)

By default, the Calendar is NOT set to Fade in or out. You can tell it how you want it to display for any 4 combinations of Fading in or out. For example to Fade the Calendar in, but NOT fade it out you would use the following Code:

Sub PopUpCalendar_Fade_Example()
 
    Call Calendar.Init(FadeIn:=True, FadeOut:=False)
 
    Call Calendar.Popup
 
End Sub
                            

You can adjust the Fade speed (whole integer, default = 6) either faster or slower by setting the 'FadeSpeed' Parameter in the Code. I have made the Calendar fade in much slower by reducing the Parameter to '3' like this:

Sub PopUpCalendar_FadeSpeed_Example()
 
    Call Calendar.Init(FadeIn:=True, FadeOut:=False, FadeSpeed:=3)
 
    Call Calendar.Popup
 
End Sub
                            

The Calendar can be used as Modal (default = True), meaning that it can only be dismissed by the {ESC} Key if required or a Mouse Click on the Dialog or by selecting a Date or any of these combinations - a Mouse Click anywhere on a Cell in a Worksheet will NOT dismiss the Calendar

To force the user to only select a Date (allow different Calendar Months to be chosen), prevent any Dialog or Worksheet Cell Mouse Clicks, but still allow the {ESC} Key to dismiss the Dialog, you can use the followiing Code:

Sub PopUpCalendar_Modal_Example()

    Call Calendar.Init(FadeIn:=True, _
                       FadeOut:=True, _
                       FadeSpeed:=6)

    Call Calendar.Popup(LeftAdjustment:=-60, _
                        TopAdjustment:=10, _
                        DismissByEscape:=True, _
                        UserFormDismissByClick:=False)

End Sub
                            

Please Note: using CTRL+Break will pause or cause the Code to Break when using a Modal Dialog. I may prevent this in future builds but right now it does not affect the operation of the Calendar which continues just fine after pressing 'F5' or continue in the VBA Editor

The Calendar can also be used as Modeless, meaning that a user may select other Worksheet Cells to pick a Date from the Dialog or to allow the user to dismiss the Calendar if any Worksheet Cell is selected. This is a neat option as you can prevent all Mouse Clicks other than the very first Cell selection to trigger the Calendar. The user cannot dismiss the Calendar by a Mouse Click on the Dialog itself, but can select a Date. If they don't want to do the Date selection now, they simply make a Mouse Click anywhere in the Worksheet and the Calendar will be dismissed. You can of course combine this with allowing or NOT allowing the {ESC} Key to dismiss the Calendar for a very professional 'pop up' Calendar

Here is the Code for a Modeless Calendar ensuring NOTHING can dismiss the Dialog only a Mouse Click on the Worksheet or a valid Date selection (this must be added into a Worksheet Code Module):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Application.Intersect(Range("C10"), Range(Target.Address)) Is Nothing Then
        If Not Calendar.Running Then
            Call Calendar.Init(FadeIn:=True, FadeOut:=True, FadeSpeed:=6)
            Call Calendar.Popup(LeftAdjustment:=-60, TopAdjustment:=10, DismissByEscape:=False, UserFormDismissByClick:=False, ModalDialog:=False)
        End If
    Else
        ' force an explicit exit
        Calendar.Running = False
        Unload frmCalendar
    End If
End Sub 
                            

Please Note: using CTRL+Break will NOT pause or cause the Code to Break when using a Modeless Dialog

By default the Date is output as a Date Serial number ie. 43355, meaning 12 September, 2018. You can apply Cell Formatting in Excel, before you pick a Date from the Calendar or you can explicitly tell the Calendar what Format to use when outputting a Date. For example to use the Short Date Format "12/09/2018" you would use Code like this:

Sub PopUpCalendar_CellFormat_Example()
 
    Call Calendar.Init(FormatActiveCell:="dd/mm/yyyy")
 
    Call Calendar.Popup
 
End Sub
                            

You can use the {ESC} Key to dismiss the Calendar (default = True). To prevent the user using the {ESC} Key, set the 'DismissByEscape' Parameter in the Calendar.Popup() Subroutine to False like this:

Sub PopUpCalendar_EscapeKey_Example()
 
    Call Calendar.Init
 
    Call Calendar.Popup(DismissByEscape:=False)
 
End Sub
                            

When using a Modeless Calendar, the {ESC} Key will still close the Dialog by default when a user selects a different Cell than the trigger Cell. By explicitly setting the 'ModalDialog' Parameter in the Calendar.Popup() Subroutine to False will force the Code to 'Map' the {ESC} Key to a Function that will allow you to still capture {ESC} (if you want to when a user selects a different Cell - you don't have to), but will also allow you to instead, let the user dismiss the Dialog by selecting a different Cell. The best way to see this is to look at all of the Examples to help you understand what I mean and how different you can make the PopUp Calendar {ESC} Key behaviour

You can prevent a user from dismissing the Calendar by a Mouse Click (default = True) by setting the 'UserFormDismissByClick' Parameter in the Calendar.Popup() Subroutine to False like this:

Sub PopUpCalendar_PreventUserFormDismiss_Example()
 
    Call Calendar.Init
 
    Call Calendar.Popup(UserFormDismissByClick:=False)
 
End Sub
                            

You can change the Calendar Month using the tiny arrows on the Calendar. The left arrow will give display the previous Month and the right arrow will display the next Month

By default the Month Format on the Calendar means that the Month and Year are displayed like this 'September 2018'. To change this to a shorter Month and Year ie. 'Sep 19', you can use the following Code:

Sub PopUpCalendar_MonthFormat_Example()
 
    Call Calendar.Init(MonthFormat:="mmm yy")
 
    Call Calendar.Popup
 
End Sub
                            

You can change the default Date that is displayed when the Calendar Pops up by specifying the 'StartDate' Property before a call to Calendar.Init() using the DateSerial function. In the Code shown below for the example Worksheet Code Module Code, if a user clicks anywhere below Row 7 on a Sheet the Calendar will be set to the current Month + 1 ie. next Month. You can also set it directly ie. DateSerial(2021, 11, 1)

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Row > 7 Then
        Calendar.StartDate = DateSerial(Year(Date), Month(Date) + 1, 1)
        Call Calendar.Init(FadeIn:=True, FadeOut:=True, FadeSpeed:=12, FormatActiveCell:="dd/mm/yyyy")
        Call Calendar.Popup(LeftAdjustment:=-60, TopAdjustment:=10, DismissByEscape:=True)
    End If
End Sub

' specify the exact Date like this meaning November 2021 - always set the Day to 1, the first Day of the Month
Calendar.StartDate = DateSerial(2021, 11, 1)

                            

The Calendar limitations are that it cannot be scaled, the Calendar Months cannot be changed into a different language and the Weekends are set to Saturday/Sunday. The Calendar uses an Image pre-loaded into the UserForm. Some parts of the Image are used as the Text for the Calendar Months and the Weekends are also pre-set. The current Calendar image and all of the UserForm Controls are at 67%. I do have a working UserForm scaled to 75% available on request if you need a slightly larger Calendar. If you have purchased the PopUp Calendar Software, you can request this from via my Facebook Page

I may address some of these points in future builds, but it is not for discussion here or via Support

This is a Screen Shot of the Popup Calendar being used to select a Date on a Worksheet

You can specifiy the Cell for the Calendar to 'popup'. Here I created a simple demo whereby the user can select a Date if they click on Cell "D5"

This is a Screen Shot of the Download File

The File includes a licensing Worksheet, showing the latest Version number and the 5 example Worksheets that contain Code for each of the Popup Calendar demos

This is a Screen Shot of the Code Modules and the main Popup Calendar UserForm UI

The UserForm has a single Image loaded as a Picture. Part of the Image is set to a distinct Colour (an off-black) which is then made invisible by the Code to get the nice 'popup' look. The UserForm contains 37 Label Controls for the Calendar Days, 2 more Label Controls for the Calendar Arrows and 1 more Lable Control for the Calendar Month

Video of the PopUp Calendar in action

Here, I run through the 5 example Worksheets to demonstrate the Popup Calendar. Remember you can use the Calendar as a Modal Dialog or a Modeless Dialog. I prefer the Modeless Dialog, where the user can click any Cell to dismiss the Calendar. I also turn off the ability to dismiss the Calendar by a Mouse Click anywhere on the UserForm, forcing the user to either select a Date, press the {ESC} Key or use a Mouse Click anywhere on the Worksheet to finish picking a Date