How to use a Pop-Up Calendar macro by selecting a cell

[savedguitarist]

I have searched this forum and have tried to apply similar answers to my coding, but I can't seem to get this to work. This is my first VB coding project. I don't even understand all that I have done; I just followed directions from a website.

Task: Having a pop-up calendar appear when selecting a specific cell.
Purpose: To select dates that reflect the work week. This is on an excel spreadsheet that I am trying to create to log work hours.

The current spreadsheet is an XLAM file that I created to be my Add-In.

The calendar I am using is one that I created with the following codes:

Under Forms, I have frmCalendar:

Private Sub cmdClose_Click()
Unload Me
End Sub

Private Sub MonthView1_DateClick(ByVal DateClicked As Date)
On Error Resume Next
Dim cell As Object
For Each cell In Selection.Cells
cell.Value = DateClicked
Next cell
Unload Me
End Sub

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Me.MonthView1.Value = ActiveCell.Value
End If
End Sub

Under Modules, I have Module 1:


Sub OpenCalendar()
frmCalendar.Show
End Sub
<strike></strike>
Under This Worksheet:

<strike></strike>
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("Cell").Controls("Insert Date").Delete
End Sub

Private Sub Workbook_Open()
On Error Resume Next
Dim NewControl As CommandBarControl
Application.OnKey "+^{C}", "Module1.OpenCalendar"
Application.CommandBars("Cell").Controls("Insert Date").Delete
Set NewControl = Application.CommandBars("Cell").Controls.Add
With NewControl
.Caption = "Insert Date"
.OnAction = "Module1.OpenCalendar"
.BeginGroup = True
End With
End Sub

<strike></strike>

These codes allow me to have a pop-up calendar when I right-click a cell and select "Insert Date" and when I press ctrl+shift+c.

I would like to add additional code to this add-in macro to enable the calendar to pop up when a user selects the cell that I designate as the "date" cell.

Would someone help me out with the additional coding to make this possible?

Thanks,
Ben

 

[savedguitarist]

Could someone please help me with this?