Today I got a challenge to provide a new function in Excel dashboard. I am not an expert in VBA and have very less experience but it’s fun doing coding in VBA. It makes excel function so simpler that I really loved it. My few lines of code helped me to reduce 3 hours manual work to only 5 minutes. Thanks to Microsoft for providing such a powerful tool.

Today I am going to explain a feature which is currently disabled in office 2010 or latest office but may be required in some project. As title suggests, lets see how we can insert a VBA calendar in Excel without having ‘Microsoft date and time picker control 6.0’ in-built function. Before starting anything let us thank Ron de Bruin for providing the encrypted code (Excel Add-in code).

Step 1: Go to Ron’s website and download the Date picker add-in. Click here to download.

Step 2: Unzip it and save it somewhere. I would prefer to save it to “C:\Users\PCName\AppData\Roaming\Microsoft\AddIns”. For example in my case the path would be “C:\Users\Deepesh.Singh\AppData\Roaming\Microsoft\AddIns”
But you can save it anywhere. I would tell you later why I am saying to save it to this path.

Step 3: Now open any excel file. You can open a new excel as well. Go to File>>Options>>Add-ins

Step 4: Check the footer. You can see ‘Manage’ option. Select ‘Excel Add-Ins’ and Go.

Step 5: Now click on ‘Browse’ and give the path where you saved the .XLAM file (Ron’s code). Now you might have got why I was talking about the above path. If you have already the saved the file somewhere else, you can paste it here again or can directly give the link.

Step 6: Click Ok, Ok…

Step 7: The option “Excel for Windows Date Picker” should be enabled/ticked. By default it does so no need to worry.

Step 8: Now right click on any cell. You will have an option to select ‘Date picker’. Nice job. We have done it. Save the excel file if you want or let it be. We just wanted to enable the ad-in which we did.

step 9: Let’s move one step ahead and do some more automation and call Date picker on any cell click. For that you can download my Excel worksheet and check code. I am using the code from Ron’s website only.

Download workbook


'calling date picker xlm code
Sub CallDatePicker()
Dim TestWkbk As Workbook
Dim obj As Object
If Val(Application.Version) >= 12 Then
Set TestWkbk = Nothing
On Error Resume Next
Set TestWkbk = Workbooks("WinDatePicker.xlam")
On Error GoTo 0
If TestWkbk Is Nothing Then
MsgBox "Sorry the Date Picker add-in is not open."
Else
Application.Run "'" & TestWkbk.Name & "'!OpenDatePicker", obj
End If
End If
End Sub


I want to call this function whenever I click “B2:B20” cell.

'Initialize the function
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
n_row = ActiveCell.Row
n_col = ActiveCell.Column
'Enabling the Date picker pop-up on the particular column
If (n_row > 1 And n_col = 2) Then
Call CallDatePickerFromOtherWorkbooks
End If
End Sub


Thanks for reading. Let me know if you have some better solution. Looking forward for your comments.

Signature

Deepesh Singh