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

savedguitarist

New Member
Joined
Nov 1, 2016
Messages
2
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,971
Messages
5,767,389
Members
425,410
Latest member
SmittyT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top