Kapat

Add Date To Cell Easily With Excel Userform

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: excel date userform
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim sectin As Variant
If Target.Column = 2 And Target.Row > 1 Then
...
Call DisplayUserForm
End If

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.

excel vba target column

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 : excel assign macro to label
Private Sub UserForm_Initialize()
Dim m, n As Byte
n = 1
For m = 2 To 8
Me.Controls("label" & m).Caption = WeekdayName(n, True, 2)
n = n + 1
Next
...
End Sub

We added the month names to the dropdown list with the following loop : excel add item to combobox
For i = 1 To 12
ComboBox1.AddItem MonthName(i, 0)
Next i

Date format can be selected with option button controls on userform . excel date format mm.dd.yyyy While entering the date into the cell, one of from the date format of mm.mm.yyyy or mm.dd.yyyy can be selected.

vba today colorToday’s date and weekends are indicated in different colors on the userform. For the weekends color change, we first add Const weekend_different_color As Boolean = True constant at the top of the userform code. We created a procedure named 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 ;
If weekend_different_color = True And left> = 95 Then
.BackColor = 9434879
End If

To indicate today’s date in a different color :
If yil = Year(Now) And ay = Month(Now) Then
Frame1.Controls(Day(Now) - 1).BackColor = 55295
End If

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.

excel vba get date from cell

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.) : excel add code to workbook

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

vba drag drop module 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. excel drag drop module in vba window

📥 Date userform can be downloaded here