I ended up doing it another way. I'll show you how I did it in case this helps anyone else. First go to this guys website and download the file and follow his tutorial all the way up to the point where you import the file into your work book. https://trevoreyre.com/portfolio/excel-datepicker/ Now if you know how to write code then just keep following what he says. But if your like me and don't know how or know very little you can do this. In excel right click on the sheet and select view code. This brings up VBA. Right click on your workbook in the left window and choose import file. Select the CalendarForm.frm file that you unzipped from his download. Now left click on the sheet you want the calendar to work in. Copy and paste this code in VBA on the window on the right...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim userSelectedDate As Date
'Show calendar form if selected cell falls within "DateRange" named range
If Not Intersect(ActiveCell, Sheet2.Range("DateRange")) Is Nothing Then
If IsDate(ActiveCell.Value) Then userSelectedDate = ActiveCell.Value
'Call CalendarForm
userSelectedDate = CalendarForm.GetDate(SelectedDate:=userSelectedDate)
'Make sure user selected a valid date from the CalendarForm
If userSelectedDate <> 0 Then ActiveCell.Value = userSelectedDate
End If
End Sub
Make sure you change the part that says sheet2 to whatever the name of your sheet is. Also You need to select the cells back in excel that you want this date picker to work in when you click them and name them DateRange or name them whatever you want and change the part of the code that says DateRange to that. It should work now.
I'm using excel 2010 but it should work in other versions I think. Also this works good if your sending the spreadsheet to other people. They don'y have to download a file or anything, they just have to enable macros when the file first starts. In the box.
