Hi

I tried to create a pop-up calendar with the help of VBA code. but an option was add in MS Excel 2013 right click context menu, now I want to remove it from context menu. Here I attached the screenshot. also need help how come I create a pop up calendar in Excel 2013 program

Thanks

Urme

 

                           

Re:   right-click menu

You should be able to remove right-click menu items with the following code.
The "caption text" used in the code must match exactly with what is displayed on the menu item...

  Application.CommandBars("Cell").Controls("enter menu item caption here").Delete

'---
Jim Cone
Portland, Oregon USA
free & commercial excel programs (n/a xl2013)
https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

Hi,

Please see this sample file:

http://1drv.ms/1DOsHBv

More steps:

First lets design the user form, so start up Excel and bring up the Visual Basic editor and add an user form in the project.

We would need the Microsoft Date and Time Picker control for this project, so please ensure that you have the required file available on your system. If it is not available you may download the MSCOMCT2.OCX from this link.

http://activex.microsoft.com/controls/vb6/mscomct2.cab

Installing this file is pretty simple, you need to extract the contents form the CAB file and then copy this into your System32 folder and then register using the REGSVR32 utility.

If you are using Windows 7 or above you would need to copy this file into the SysWOW64 folder and then register.

For Windows 7 and above, please make sure you are running the Command Prompt (Admin) to be able to successfully register the ActiveX control.

Windows 7: Click on Start, All Programs, Accessories, Command Prompt (right click and choose Run as Administrator
Windows 8: Windows Key + X, then choose Command Prompt (Admin)

Okay, lets get back to designing the user form.
Insert a new Userfrom on the VBA project and then click on Addition Controls on the Tools menu.

Once the Additional controls dialog box is on the screen, locate the above highlighted entry and then select the same by clicking the box on the left. Now click Ok to close this dialog box.
Now place one Monthview control on the userform and one Command button.
Below are the properties that we need to change for the Commandbutton
Caption = Close
Cancel = True
Name = cmdClose
Place this command button anywhere you like on the userfrom, we will place the Monthview on top of this to avoid show this to the user.
Since we have specified the Cancel = True for the commandbutton, the click event can be triggered by pressing the Escape key to handle the code that we will write for the Close button.

We are done designing the Userform, now we need to write the code to handle the events.
Below is the code
Close Button

Private Sub cmdClose_Click()
Unload Me
End Sub

Userfrom

Private Sub UserForm_Initialize()
'matching the date in the calendar with the date of the active cell
'if there is a date,
If IsDate(ActiveCell.Value) Then
Me.MonthView1.Value = ActiveCell.Value
Else
Me.MonthView1.Value = Now
End If
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

What the above code does?

1. The close button code will simply unload the userform and take it off the screen.
2. The userform initialize event code will check if the current cell on which we are right clicking the mouse contains any date, if there is a date then it will set the date on the calendar as the one on the cell, otherwise it will show todays date.
3. The dateclick event of the Monthview control occurs when we click on any date, this code is responsible for populating the cell with the date we have selected. If there are multiple cells selected the code will populate all of them with the date selected.

Adding the context menu option

Now comes the interesting part of adding the context menu option, one thing I would like to specify here as the name suggests Context menu these options change depending on what and where we are right clicking the mouse. You will see a different context menu when you right click on a cell, table, shape etc.

Since every object has a different type of context menu associated we need to make site we are adding our option to the right place.
I would recommend reading this article to know more about the types of commandbars available and how to use them. http://msdn.microsoft.com/en-us/library/office/aa141001(v=office.10).aspx

We wanted to add the right click context option to a data table which is called as List Range Popup.

Create a Data Table

Type the heading in Cells
B2 = ID
C2 = Start Date
D2 = End Date
E2 = Name

Now click on cell B2, and press CTRL + T shortcut from the keyboard. Make sure to select the option My Table has headers and then click Ok.

We would need the add the below code to the Open event of our workbook so that this option is available to us every time we need to work here.

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

We have also assigned a shortcut key of CTRL + SHIFT + C to this, for those who love to work more using the keyboard.

The above code will add the Insert Date context menu option to our data table(s) in the active workbook whenever we open this file.

Next is cleanup
We need to make sure that the context menu we have added is also removed when the file is close, the below code will do that for us.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnKey "+^{C}"
Application.CommandBars("List Range Popup").Controls("Insert Date").Delete
End Sub

Regards,

George Zhao
TechNet Community Support