We have developed a small but very resourceful userform to add and change the date to the active cell. Our userform is a useful alternative to date picker that doesn’t need any .ocx files. By double-clicking the cells in the selected column, the date userform opens and when one of from the buttons that created for each day of the month are clicked, the date is added to the cell.
If it is wanted to add a date on which sheet using the date form, the necessary VBA codes should be added to the Worksheet_BeforeDoubleClick procedure of that sheet. In our example template, we decided that it is appropriate to display the userform by double clicking on any cell except cell 1 in column B. For this purpose, we added the following codes:
We specified that we want to show the userform in column B with
Target.Column = 2 section of the codes, but with
Target.Row > 1 code snippet we ensured that B1 (1st cell) will be excluded from this operation.
Day and month names in the date userform are created according to the system language of the user.We added with a For-Next loop to the Userform_Initialize procedure to assign day names to label controls :
We added the month names to the dropdown list with the following loop :
Today’s date and weekends are indicated in different colors on the userform. For the weekends color change, we first add
date_setting() that to set the userform size according to the number of days and to set color changes on the userform.
In this procedure we set the weekend colors with the following codes ;
To indicate today’s date in a different color :
Another important feature of date add userform is; If there is a date in the selected and clicked cell, the days, month and year are adjusted according to that date and it is displayed on userform to user.
Also, the format of the date in the cell is indicated with option buttons.
If the user wants to add date with userform to column B of all sheets of the workbook, they should add the following codes to the workbook in VBA Window (After adding VBA codes to workbook, codes in worksheet can be removed.) :
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) Dim sectin As Variant If Target.Column = 2 And Target.Row > 1 Then Cancel = True tarih = Empty sectin = Split(Target, ".") If UBound(sectin) = 2 Then On Error Resume Next tarih = DateSerial(sectin(2), sectin(1), sectin(0)) On Error GoTo 0 End If Call DisplayUserForm End If End Sub
To use the date userform in your own workbooks, open your own workbook with our template open, then press Alt + F11 to open the VBA Window. Here, drag and drop the date_Form, module and class file to your own workbook.