Fast Date Entry To Active Cell With Right-Click Menu

Userform To Insert Date (Alternative To Date Picker and Calendar Control)

        In our template ,firstly we first added a new item - DATE FORM - to the right click menu using VBA codes :
Sub Addmenu()
Application.CommandBars("Cell").Reset   'Right click menu will be reset.
With Application.CommandBars("Cell").Controls
With .Add(Before:=1)
   .Caption = "DATE FORM"  'Menu caption you want to add
   .FaceId = 39
   .OnAction = ThisWorkbook.Name & "!MacroName"  'Macro you want to run
   .Tag = "MacroName"
   .BeginGroup = True  'New Group
End With
End With
End Sub

When the workbook is opened , the new item is automatically added  with the Auto_Open method to the right click menu :
Sub Auto_Open()
Call Addmenu
End Sub

This new item that we added is removed through Auto_Close method when the workbook is closed  :
Sub Auto_Close()
Call menureset
End Sub

Sub menureset()
Application.CommandBars("Cell").Reset 'Right click menu will be reset.
End Sub

        We added all the methods related to the right click menu to the VBE window by adding a new module (Module1) and pasted it there. Thus ,the right click menu can be used on all pages of the workbook with the addition of new item.

        When right-clicked on the active cell, context menu is opened.
We click on the "DATE FORM"  that we created via the module in the context menu.
When the date userform is opened, it will automatically appear today's date. If you wish, you can select another date with the help of the drop-down boxes to enter the cell.

        At the bottom of the userform, you can see which day the selected date corresponds to.
You can use the date form on all sheets of the workbook.

 The userform contains three combo boxes (for month,day,year).

         The date selected from the combo boxes is displayed as short date ( in the textbox. The day name for the date is reported in the label control on textbox's right ; sunday,monday etc. Related codes :
TextBox1 = VBA.Format(DateSerial(VBA.CLng(Me.cboYear.Value), Me.cboMonth.ListIndex + 1, Me.cboDay.Value))
Label1.Caption = WeekdayName(Weekday(TextBox1, 0), False, 0)
          When the button is pressed, the selected date is entered into the cell and userform closes.
To use the date form in your own workbooks, copy and paste the module and date form.

Userform To Insert Date -Alternative To Date Picker and Calendar Control

No comments:

Post a Comment