I programmed in VB since 2005, and I thought I mastered it, till today, I am humbled. More than a week now I searched over the net how to do exactly this, and the only option I found was the TDatePicker. But the problem was it appears in front of the cell! But this, what you shared, is exactly, exactly, yet EXACTLY what I had in mind, but struggled to do it. You are a divine messenger, and earned my subscription. You are the very first channel I registered yet I am then #147 of your subscriber. God bless my brother, waiting for the next video agani
Bro, you're a genius! I've been looking for a calendar like this for a long time, and only your version works as it should. Thank you so much for your hard work!!!
First of all thank you VERY much for an outstanding piece of code! Anyone with the 64 bit version of office will be eternally thankful for a functioning date picker. However, there is one change to the code I would strongly suggest you implement in your version. The datepicker shows up whenever a cell is formatted as a date. If you reference a date via a formula someone might inadvertantly overwrite the formula with the date picker. A simple fix is to test for a formula in a cell and if so NOT display the datepicker. it can easily be fixed in the Class Module - DatePicker Manager in the function checkForGridDP: replace the line If VBA.IsDate(ActiveCell) Or ActiveCell.NumberFormat Like "*[Dd]*" Then with: If (VBA.IsDate(ActiveCell) Or ActiveCell.NumberFormat Like "*[Dd]*") And Not (ActiveCell.HasFormula) Then This will fix this issue. There is one more thing which would be a major enhancement. The original ActiveX datepicker allowed for a specific cell to be referenced. So DatePickers on different sheets could change the same "global" date cell which was really helpful. This is impossible to achieve with the current implementation. The DatePicker form would have to include a field where the reference cell could be entered. When left emptpy, the date would be applied to the selected cell as it works right now. Just some thoughts and thanks again!
Ive been doing VBA/Excel almost day to day for 10 years and this just proves there's still more to learn. I had a different idea but this was so elegantly and wonderfully presented I didn't see any other reason to try and homebrew my own idea. This was a wonderful tutorial and creativity for this solution was off the charts. Great stuff man.
Such a clean implementation! This blows my previous attempts out of the water. Works flawlessly and I was even able to copy the modules and form to my personal macro workbook so its functionality is available all the time without installing an add-in. Thank you!
Just found this now as I was looking for a clean way to put a calendar on our process from. Video is exceptional and I also downloaded the add in and made life a little easier. Thank you for BOTH.
Sam you Rock. Everybody must definitely download the version built into a workbook in the Q&A section on his site. Works like a charm. My go to version from now on.
cam on ban nhieu lam luon a, ne va thuc quy nhung nguoi gioi ma con sẵn sang chia se kien thuc va kinh nghiem cua ban than cho nguoi khac, chuc ban luon thanh cong nhe
I love the Dancing sheet. This is magic, and I want add more dancers, but I'm still pretty new to VBA. Also, I may use this to make a button in our add-in to mess with my co-workers. :)
Awesome! Always great to have some fun with co-workers! I added another dancer as an example for you, its on the site as an additional link, and I now describe how to replace / update them. Thanks for the comment! samradapps.com/dancing
I'm struggling with this. I need to create a sheet that has a few columns that need dates. I was hoping to simplify the task. I'm far from being a VBA expert but hoped to download the file and be able to work it into the sheet I needed without the need to write all the code. I downloaded the file named "date picker Video" and started it. I also unblocked the security feature in the properties tab for the file and enabled macros if asked. When clicking on the blue button I get a popup that says, 'Specified DLL function not found'. If I select OK teh calendar shows but it is not a finished calendar as shown at the 10:57 mark of this video. Clicking away into another cell then throws out a "MS Visual Basic Run-time error '1001': Application defined or object-defined error". What am I doing wrong? I also downloaded and unblocked the file "dp blank" This file opens but typing the date or manually entering the date does absolutely nothing. I see the Home ribbon has the Date Picker button, this evokes errors such as Can't find DLL entry point FindWindowA in user 32. Does this not work on 64 bit versions of Excel?
Hi there. Thank you for this! This is a great tool to use and I am happy I came across this video. I have a problem with my code. I have done exactly what you have, and for some reason, at the 14:45 mark, when I select the cell, it shows the month and year, but when I go to another cell that is above or below the first cell that was active, the labels revert back to Month and 20XX. Not sure why this is happening? I checked for syntax errors etc. but I do not see what I have done differently to your code.
Thank you very much for your very useful video. I have a question. How can I change the order of the days of the week? For example, instead of Sunday, Monday is the first day shown on the left? Thank you.
the add on works really good! thanks for sharing this. One issue I found, When I enable the "show in grid" feature, which means the icon will be displayed in the cell, the autofill options buttons will disappear...anyway we can fix it?
This is amazing and very helpful! Would love to know how to change the color of the calender itself. Instead of a Dark Red/Maroon-ish color if I wanted to change it where would it go in the code?
Thanks! For the color, the easiest way: if you open the datepickerform in the VBA editor, you can select the control redBG and set the backcolor property and the bordercolor property to the color you want in the property picker. Then press the save button in the VBA editor. You could also add some code to the UserForm_Initialize() to set to color, such as redBG.BackColor = 12632319
@@radsheets the small calendar icon that appears in the worksheet when you click on the cell have turned to a blue square and I can not fix it, can you help me with that please ?
Hi Sam , I use the Data Picker add-in in my excel application. Thank you very much. But I have a problem, the plugin accepts "Sunday" as the first day of the week. Is there a way to change it to "Monday"? Can you help with this?
Hi, awesome work, use it all the time. If possible can you add code to change screens? As I move from left screen to right screen the calander pops up on left screen at the bottom. Any help appreciated
Hi @radsheets Is that normal to loose the Undo history because this Add-in? I also have been finding small calendar icons without any function when I open again some files and I have to delete them manually. Why that is happening?
Thanks man for this amazing tutorial. I'm extemelly shocked actually and wondering how on earth such a simple feature is not already build in the latest version of Excel from Microsoft Office professional 2021 which costs around 439.99 € officially from Microsoft. At least its not on 64bit version.
Seing that Samuel is soooh good at VBA.....maybe we can convince him to do a video and to write us an searchable dropdownlist that recognizes cells that has data validation in them and can work on all versions of excel. Just wishing out loud.
Thank you so much for the awesome job. Still I cannot wheel scroll through the years. Also the up and down buttons only move 3 years every time you click, instead of taking you to the next bundle of 12 years. Any help? thanks in advance!
I am using similar approach to have some other function, however the icon part is not clear to me. what is the right steps to convert the image into bmp then hex? or what is the best approach, please help
Hi, first of all thanks for the work on this great feature! It's a great addition, however, I've encountered a strange issue when using the date picker. After entering a date, the history for the undo is erased. Is there a fix for this? Thanks!
GREAT JOB. However it seems only to work if Excel runs on the Windows Main Display. In an environment with multiple displays, the calendar doesn't follow the mouse position when Excel runs on a different display. The calendar will always appear on the windows main display, which makes the date picker use very cumbersome.
beautiful work! is it possible to use this calendar to select a date, then move the active cell that contains the date the is selected on the calendar?
What an amazing feature! Just a question, does the calendar only appear in the cell right below the last date you've added, or is it possible to show the calendar for each cell of an entire column? I mean, if for some reason the person skips a cell and wants the calendar to appear in the other cell, it doesn't appear on my file, but it could be me not knowing how to use it
Thank you Lara 🙏Interesting idea, there are a couple ways I can think of, maybe if you adjust the VBA code to check if the cell is formatted for date entry, that could help. Or on cell selection, walk up and see if you run into a date to show it. Those could make nice future videos. In the mean time, if you don't want to change the code, you could use the right click entry point, or the ribbon entry point on the home tab ... not quite the same, but hopefully it can help!
@@radsheets Thanks for creating this feature. I have the same issue as Lara. I want to force the user to input a date in a cell for a particular column. However, if the cell is more than 1 cell below a date, the icon does not appear. Where in the code do I go to change so that it looks for 5 above or 10 above? I did Alt + F11 and could not figure out how to adjust, mostly because I have no idea what the coding means and didn't know what to look for. Thanks!
Hey Jennifer! Thanks for commenting on it. I did a quick update to the date picker just now to check for a date number format in the cell ... so if you have a date format in the cell the grid icon should appear even if no dates are around it. There isn't a perfect way to check for a date format, so I did a more generic way that should work in most scenarios. The change is in function "checkForGridDP" which has logic to see if it should show the grid date picker icon based on the cell's number format. Let me know if it works!
Wow! wonderfull. Using the addin and works like a charm. Is there a way I can load the "addin" straight into my worksheet as computers at work dont allow addinns?
Hi Francois! You would have to copy / paste everything from the add-in into the workbook you want to use. Are you able to save the add-in to your desktop and then just dbl click to load it? Or is that blocked by your work too?
Sooh glad for your reply. all is blocked including that possibility. I tried to copy everything in and it gave errors. I realy would like to incorporate you module as it is very professional. Any way to send you a sample of my file. to see if you could assist? Maybe I did something wrong on the first try. it is realt one of the greatest date picker i have seen.
Thanks Francois! I added, to the FAQ section of the date picker download, a empty workbook with the date picker already copied into it. You can copy your items into that workbook and it should work well!
Thank you so much for all the effort. I have downloaded from the link and will give it a go today. very excited to have your datepicker in my workbook soon.
Thanks - any idea how to make the calendar interactive so we can change the current month and select days say 2/3 months in the future? Is this a simple code amendment?
Hi Sam, I have updated the original download to your latest 221114 version and have a few questions. There was a small change in this version that causes a problem and wondered whether you can explain the reason for the change? It has to do with checking whether the selected cell is a date or not and supplements the original use of the IsDate function. You now check the number format string as well which seems irrelevant to me. Furthermore, if the number format is for a number, not a date, and includes the "[Red]" qualifier for example, the letter "d" incorrectly causes this version to treat the number as a date! My other question concerns worksheet protection; if the worksheet is protected the AddIn will not detect a selected cell with a date. I have overcome this in my own workbooks by enabling VBA to run using UserInterfaceOnly, but have you thought of a solution within the AddIn itself? Thanks again for such a handy tool!
Hey Rad Sheets, this is awesome thank you for sharing. Just wondering, if I share the sheet with others, do they have access to use the date picker? or would they have to install the date picker too?
Hey. Many thanks for this great plug-in. One problem: Today is Friday 5.04.2024, Datepicler shows Saturday 05.04.2024. The wrong day of the week. Mysterious.
I have been using this amazing tool for several years in my VBA workbooks but for the last few months Excel has been crashing on me. Is there a known problem with the most recent Excel 365? The fault seems to be caused when switching worksheets after simply selecting random cells with the mouse on another worksheet. I'm using your date-picker as an Add-in.
Thanks for using the add-in! I haven’t experienced or heard of it doing that, though sometimes 365 does pick up a bug … though normally they get addressed pretty quickly like a week or so. You can try uninstalling the add-in and seeing if it continues to have issues. If you’re selecting random cells the add-in isn’t doing much so hopefully not the problem!
@@radsheets Thanks for the tip. I've updated to the latest download but same problem. I've also tried to debug the event handlers in my workbook as well as date-picker and all seems to be ok; the problem also cannot be replicated in breakpoint mode so perhaps it's a timing issue? Also, the problem cannot be replicated if date-picker is not installed! Do you have any suggestions for how I could debug this in case there's a VBA problem? I'd appreciate any further thoughts. Thank you.
Hey thanks for debugging! Sorry you’re seeing a problem! Based on your description I would try commenting out some of the API calls given those can cause Excel to crash, so the set timeout and mouse wheel detection to start and seeing if it still happens, and continue down that path of commenting out APIs … you could also try the date picker in the user form and see if that reacts, it’s similar code … sorry I can’t be of more help! I know these things can be a pain!
Does anyone know how I can change the VBA code so the calendar pops up automatically when a cell is clicked in? I have found that if the cells "number" is set to "date" it shows the Small Calendar icon to show the date picker. I would like for the date picker to open when the cell is clicked. I am pretty new to this but have enjoyed learning.
I've used your spreadsheet and copied it into my own so I've got the date picker working well now, except I can't scroll the month and need to change it to dd/mm/yy format. How can I do those things?
Thanks Tess! For the dd/mm/yy, the function GetDatePicked() returns the date to enter, and it pulls from datePicked which is the string of the date, so adjusting that inside that function to dd/mm/yy is a quick way. That all gets set from the tag on the labels, so changing the tags to be dd/mm/yy would work as well. For the month changing, clicking on the month name will let you pick another, if you want mouse wheel then you'd have to adjust the 64-bit api's in the api_scroll_wheel file to work (last I tried I was hitting some strange errors, but left the code there in case someone wants to adjust it).
HI Rad, I am battling to make the date picker work on my latest MS 365 software, although i have 'unblocked it as well, as recomended by you. Is there anything else that i need to do,. pls confirm Thanks Martin ( south africa)
Hello Rad! I want to know if we can change the daystart of the week. Your week starts on Sunday but mine on Monday :') Can we make this little change that hurts me a bit
Hey Rabapt! Thanks for the comment! I talk about that around 15:25, basically adjusting the startOfMonth variable to say the first day of week is Monday. You'd also have to update the top labels on the calendar, but totally do-able!
Hi, I hope you may be able to help me. First I must say I love this calendar for excel. I use lots of Excel docs for work and having this calendar has been very useful. I also use mostly a mac but recently had to purchase a laptop. Now I am using both. When creating my excel I came across your datepicker calendar and began using it on my laptop and for the first few days, I was able to use iCloud and go back and forth working on the excel entering all my data without any issues regardless if I was updating the excel on my pc or Mac. However, yesterday there was an update for excel on my mac and today I noticed my datepicker has an error and I keep getting a message to debug it. I have no idea how to do that. Are you able to help me or share a link on how I may be able to do so? Thank you in advance for your assistance.
Hello, I went thought your video example and tried to add the functionality in your Add-In (specifically month/year picker and month up/down) but I noticed that the basic implementation of both is different and was not able to add these to the code shown in the video. What I need is a picker that can have this functionality but also is specific to one (or two) selected cell(s). I was able to make some progress reverse coding some of your Add-In onto the example you show in the video but have had some issues I cannot find a way around. Namely, getting the calendar to auto-populate at the start with the current date and passing the picked date to the cell where the calendar picker lives (I can make the spreadsheet available if you want to see it). If you could post how to do this that would be of great help. Thank you in advanced.
Hi DuHast! Based on another comment I adjusted the date picker to show on pre-formatted date cells, does that help with your spreadsheet? So on the cells you want the date picker to appear, just format the cell as a date format and it will appear.
@@radsheets Hello Rad, thank you for the reply. The spreadsheet where I want to use the picker has many calculated dates that stem from the one specified in the input cell (where the date picker will live). I'm trying to make it work in a test spreadsheet first as to not have the new code become confused with the code currently in the spreadsheet. I am so close to making it work. On one hand, the calendar is not automatically populated and gives an error (invalid procedure call or argument), once the error is ignored and the month and year are picked by clicking them, then the calendar populates with the right dates. But, when clicking on a day, the date is only passed along to the cell if it is within the first 23 days displayed in the calendar. On the other hand, I can make the calendar auto populate nicely, and the month and year become unclickable as well as the days displayed, so no date is passed along to the cell. So this is the dilemma, I can make some of the features work but others break... I'm sure if you could take a look at the code you could figure it out quickly. If you want to take a look at the spreadsheet let me know and I can send it over.
Hey DuHast! If you upload it somewhere I can download and take a look. I did just upload, in the FAQ of the date picker, a download link for a blank workbook with the date picker copied into it and working. So you could start with that and add your sheets / data to it and it should work well!
Hi Sir, i want to change the ribbon or better, i want to rename it into the my native language. I dont find the code in the add in to actual do this. Can u help me?
That so amazing ! Your add-in is very helpful for me. But it will be great to me if you can change the selection by scrolling to change the time when clicking on the hour or minute like choosing the month like now Meaning that: When clicking on the month or year, it is still selected as it is in your current project. And when clicking on a date, it will select a date, and it will save that selected date. Then click on the hour or minute to select the time with scrolling the mouse wheel and select the Day - month - year - hour minute to save it down to the cell Can you help me on that?
Very good Plug-in ❤❤❤👍 my question is can I change the calendar to start on Monday, not Sunday? I would like to use the one where it starts Monday first not Sunday, in which programming line is it possible to change this? Thank you very much for the answer.
Hi @radoslavstuchlak8296, You need to change the following line in the module 'dp_core' in the sub populateWeekdayNames(). Original for Sunday: datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbSunday) New for Monday: datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbMonday) ########## Also change the following line in the module 'dp_core' in the sub populateDatePickerDays(). After the comment line: 'get the Day of the week for the start Orginal for Sunday: iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbSunday) New for Monday: iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbMonday) ########## The following still needs to be changed: In the form 'datepickerform' in the procedure - 'Private Sub UserForm_Initialize()' After the comment line 'set the current date and time Orginal for Sunday: todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbSunday)) & ", " _ & VBA.MonthName(VBA.Month(VBA.Date)) & " " _ & VBA.day(VBA.Date) & ", " _ & VBA.Year(VBA.Date) timeButton.Caption = VBA.Time New for Monday: todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbMonday)) & ", " _ & VBA.MonthName(VBA.Month(VBA.Date)) & " " _ & VBA.day(VBA.Date) & ", " _ & VBA.Year(VBA.Date) timeButton.Caption = VBA.Time ########## I have tested and used it, it works great!
@@radsheets Tested and works great with ammended code. Thanks. Can I copy only one sub that had changes into my worksheet, or must I go and put everything in again. If only one..Which one must I replace?
after a few weeks of using the widget, the small icon showing the date on the worksheet turned to a blue square and is not active anymore, how can i fix it?
I like this date picker but I don't like that it displays the calendar prompt icon on the sheet for cells that are formatted as "currency" instead of just cells that are formatted as "date." It doesn't do this for cells formatted as "general" or "text" or "percentage." Any fix for this?
hi, i have a 64bit edition of excel and really appreciate this video - i was following it all until we got to the MoveFormToMouse line, as soon as i add that, VBA highlights the ShowCal Sub line in yellow and throws an error.. where am i going wrong? also im a bit confused.. it seems after i import the add-in and put it in the xstart folder, when i click alt+f11, the datepickerform is already there, along with the api's and everything else so what is the userform1 for? is that the same thing? do i need to create a userform1 as well as having the 'datepickerform'? i dragged the date picker form over to the project with my current worksheet, and did the same for the other folders class and module.. now i think ive made a bit of a mess and have no idea if i should wipe the lot and start again, whether or not i should import the addin and use that or not bother with that and instead recreate a userform1 as you did in the first 8 mins or so of the video? sorry.. new to excel but really wanted a drop down date picker.. i dont know why Microsoft dont have one for the 64 bit edition and why they havnt bothered to make one - literally spent days just trying to create a drop down box when it couldve been done with a few clicks :( anyway, ive subbed because youre the only person so far whos even bothered addressing the issue for a 64bit edition any help would be greatly appreciated though - if you have a video which just shows what to do with the downloaded file, how to add the add in and use the vba stuff you already have within the downloaded files and what to drag where to get it to work, id really appreciate that, hopefully save me typing lots of code as i think that is really throwing me off - im bad enough with excel let alone coding on top of that :) many thanks
i managed to make sense of it, i used the FAQ as suggested by @francoismarsau6412 in one of the comments and found blank workbook i could open at the same time as my worksheet and drag the module/class etc over really happy with that, spent nearly a week of 5 or 6 hours a day to get my timesheet template done - you should definitely have way more subs that you have currently absolutely brilliant!! thank you thank you thank you thank you!!!
Hi @tommulcahy2598, You need to change the following line in the module 'dp_core' in the sub populateWeekdayNames(). Original for Sunday: datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbSunday) New for Monday: datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbMonday) ########## Also change the following line in the module 'dp_core' in the sub populateDatePickerDays(). After the comment line: 'get the Day of the week for the start Orginal for Sunday: iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbSunday) New for Monday: iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbMonday) ########## The following still needs to be changed: In the form 'datepickerform' in the procedure - 'Private Sub UserForm_Initialize()' After the comment line 'set the current date and time Orginal for Sunday: todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbSunday)) & ", " _ & VBA.MonthName(VBA.Month(VBA.Date)) & " " _ & VBA.day(VBA.Date) & ", " _ & VBA.Year(VBA.Date) timeButton.Caption = VBA.Time New for Monday: todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbMonday)) & ", " _ & VBA.MonthName(VBA.Month(VBA.Date)) & " " _ & VBA.day(VBA.Date) & ", " _ & VBA.Year(VBA.Date) timeButton.Caption = VBA.Time ########## I have tested and used it, it works great!
Hey Mohd, great question: its worth a video on its own so I'll put in in the queue, but you would have a couple ways to do it 1-build the form and reserve part of it for a date picker, and show that part so its all in one place 2-from a button on the form open up another form, which would be the date picker. Either way you can start with the workbook in the video (its in the FAQ download on the site) and decide if you want expand that form or pop it up. I'll be sure to do a future video on date picker popups in user forms! Thank you for the idea!
Ah yeah, I don't think the Mac can do the API part of the code given its Windows specific, but the other parts should work. If you remove the APIs and calls to them, should run.
Everything worked perfectly until I reached the last part where clicking on a date is supposed to fill in the cell automatically. It is not working😭 and I wrote all the codes similarly one by one as in this video. Plz help!!!! 😭😭😭
I have tried the blank workbook template on 5 computers and double checked the macro settings and unblocked the file. pop up icon not working. can I supply my email adress to contact you with a sample?
I programmed in VB since 2005, and I thought I mastered it, till today, I am humbled.
More than a week now I searched over the net how to do exactly this, and the only option I found was the TDatePicker. But the problem was it appears in front of the cell!
But this, what you shared, is exactly, exactly, yet EXACTLY what I had in mind, but struggled to do it.
You are a divine messenger, and earned my subscription. You are the very first channel I registered yet I am then #147 of your subscriber. God bless my brother, waiting for the next video agani
Whoa thanks for such a nice comment & sub! 🙏 Awesome that you've been using VB for a long time, love it! 💖
Bro, you're a genius! I've been looking for a calendar like this for a long time, and only your version works as it should. Thank you so much for your hard work!!!
First of all thank you VERY much for an outstanding piece of code! Anyone with the 64 bit version of office will be eternally thankful for a functioning date picker.
However, there is one change to the code I would strongly suggest you implement in your version. The datepicker shows up whenever a cell is formatted as a date. If you reference a date via a formula someone might inadvertantly overwrite the formula with the date picker. A simple fix is to test for a formula in a cell and if so NOT display the datepicker.
it can easily be fixed in the Class Module - DatePicker Manager in the function checkForGridDP:
replace the line If VBA.IsDate(ActiveCell) Or ActiveCell.NumberFormat Like "*[Dd]*" Then
with: If (VBA.IsDate(ActiveCell) Or ActiveCell.NumberFormat Like "*[Dd]*") And Not (ActiveCell.HasFormula) Then
This will fix this issue.
There is one more thing which would be a major enhancement. The original ActiveX datepicker allowed for a specific cell to be referenced. So DatePickers on different sheets could change the same "global" date cell which was really helpful. This is impossible to achieve with the current implementation. The DatePicker form would have to include a field where the reference cell could be entered. When left emptpy, the date would be applied to the selected cell as it works right now.
Just some thoughts and thanks again!
Ive been doing VBA/Excel almost day to day for 10 years and this just proves there's still more to learn. I had a different idea but this was so elegantly and wonderfully presented I didn't see any other reason to try and homebrew my own idea. This was a wonderful tutorial and creativity for this solution was off the charts. Great stuff man.
Such a clean implementation! This blows my previous attempts out of the water. Works flawlessly and I was even able to copy the modules and form to my personal macro workbook so its functionality is available all the time without installing an add-in. Thank you!
Just found this now as I was looking for a clean way to put a calendar on our process from. Video is exceptional and I also downloaded the add in and made life a little easier. Thank you for BOTH.
So far I watched three calendar VBA video's yours is by far the best, thank you, and great work!
Thank you for this video. I followed your tutorial and I managed to create Date Picker.
This is so impressive,your vba knowledge is outstanding.
Thank you!!
Sam you Rock. Everybody must definitely download the version built into a workbook in the Q&A section on his site. Works like a charm. My go to version from now on.
Thank you Francois for working on it with me! 🥰
cam on ban nhieu lam luon a, ne va thuc quy nhung nguoi gioi ma con sẵn sang chia se kien thuc va kinh nghiem cua ban than cho nguoi khac, chuc ban luon thanh cong nhe
Дякую!!!! Просто і ефективно у викристанні. СУПЕР
I like the way you code. I am looking forward for more videos.
Outstanding work!
Mate please finish this tutorial with all the functionalities, I'm enjoying a lot this project! Congratulations amazing video!
I love the Dancing sheet. This is magic, and I want add more dancers, but I'm still pretty new to VBA. Also, I may use this to make a button in our add-in to mess with my co-workers. :)
Awesome! Always great to have some fun with co-workers!
I added another dancer as an example for you, its on the site as an additional link, and I now describe how to replace / update them. Thanks for the comment!
samradapps.com/dancing
Health to your hands.
Thanks so much for your sharing.
Best wishes for success in your work.👍
Simply stunning. Thank you for sharing.
is excellent, the more high level in calendar for excel
I'm struggling with this. I need to create a sheet that has a few columns that need dates. I was hoping to simplify the task. I'm far from being a VBA expert but hoped to download the file and be able to work it into the sheet I needed without the need to write all the code. I downloaded the file named "date picker Video" and started it. I also unblocked the security feature in the properties tab for the file and enabled macros if asked. When clicking on the blue button I get a popup that says, 'Specified DLL function not found'. If I select OK teh calendar shows but it is not a finished calendar as shown at the 10:57 mark of this video. Clicking away into another cell then throws out a "MS Visual Basic Run-time error '1001': Application defined or object-defined error". What am I doing wrong?
I also downloaded and unblocked the file "dp blank" This file opens but typing the date or manually entering the date does absolutely nothing. I see the Home ribbon has the Date Picker button, this evokes errors such as Can't find DLL entry point FindWindowA in user 32. Does this not work on 64 bit versions of Excel?
Nice stuff... and i like the way you bring it clear, sharp and fast 👍
Thank you!! 🎉👍
Brilliant! Thanks for sharing!
Thank you! 🙏
Super thanks to you!!!! Great stuff.
Hi there. Thank you for this! This is a great tool to use and I am happy I came across this video. I have a problem with my code. I have done exactly what you have, and for some reason, at the 14:45 mark, when I select the cell, it shows the month and year, but when I go to another cell that is above or below the first cell that was active, the labels revert back to Month and 20XX. Not sure why this is happening? I checked for syntax errors etc. but I do not see what I have done differently to your code.
Thank you very much for your very useful video. I have a question. How can I change the order of the days of the week? For example, instead of Sunday, Monday is the first day shown on the left? Thank you.
the add on works really good! thanks for sharing this. One issue I found, When I enable the "show in grid" feature, which means the icon will be displayed in the cell, the autofill options buttons will disappear...anyway we can fix it?
Awesome job ! There could be a whole series based on the techniques used in your datepicker
OMG. Just Brilliant.
This is amazing. Thank you very much for share.
This is amazing and very helpful!
Would love to know how to change the color of the calender itself. Instead of a Dark Red/Maroon-ish color if I wanted to change it where would it go in the code?
Thanks! For the color, the easiest way: if you open the datepickerform in the VBA editor, you can select the control redBG and set the backcolor property and the bordercolor property to the color you want in the property picker. Then press the save button in the VBA editor. You could also add some code to the UserForm_Initialize() to set to color, such as redBG.BackColor = 12632319
@@radsheets the small calendar icon that appears in the worksheet when you click on the cell have turned to a blue square and I can not fix it, can you help me with that please ?
Hi Sam , I use the Data Picker add-in in my excel application. Thank you very much. But I have a problem, the plugin accepts "Sunday" as the first day of the week. Is there a way to change it to "Monday"? Can you help with this?
Hi, awesome work, use it all the time.
If possible can you add code to change screens? As I move from left screen to right screen the calander pops up on left screen at the bottom. Any help appreciated
Genius thank you!
Excellent. Thanks 😊
Awesome thanks for the comment! 😀
Hi @radsheets Is that normal to loose the Undo history because this Add-in? I also have been finding small calendar icons without any function when I open again some files and I have to delete them manually. Why that is happening?
Thanks man for this amazing tutorial. I'm extemelly shocked actually and wondering how on earth such a simple feature is not already build in the latest version of Excel from Microsoft Office professional 2021 which costs around 439.99 € officially from Microsoft. At least its not on 64bit version.
Seing that Samuel is soooh good at VBA.....maybe we can convince him to do a video and to write us an searchable dropdownlist that recognizes cells that has data validation in them and can work on all versions of excel. Just wishing out loud.
Humm interesting, could be a future video topic, thank you for the idea!!
Thank you so much for the awesome job. Still I cannot wheel scroll through the years. Also the up and down buttons only move 3 years every time you click, instead of taking you to the next bundle of 12 years. Any help? thanks in advance!
I am using similar approach to have some other function, however the icon part is not clear to me. what is the right steps to convert the image into bmp then hex? or what is the best approach, please help
Hi, first of all thanks for the work on this great feature! It's a great addition, however, I've encountered a strange issue when using the date picker. After entering a date, the history for the undo is erased. Is there a fix for this? Thanks!
مرسی
GREAT JOB. However it seems only to work if Excel runs on the Windows Main Display. In an environment with multiple displays, the calendar doesn't follow the mouse position when Excel runs on a different display. The calendar will always appear on the windows main display, which makes the date picker use very cumbersome.
beautiful work! is it possible to use this calendar to select a date, then move the active cell that contains the date the is selected on the calendar?
excelente video
What an amazing feature! Just a question, does the calendar only appear in the cell right below the last date you've added, or is it possible to show the calendar for each cell of an entire column? I mean, if for some reason the person skips a cell and wants the calendar to appear in the other cell, it doesn't appear on my file, but it could be me not knowing how to use it
Thank you Lara 🙏Interesting idea, there are a couple ways I can think of, maybe if you adjust the VBA code to check if the cell is formatted for date entry, that could help. Or on cell selection, walk up and see if you run into a date to show it. Those could make nice future videos. In the mean time, if you don't want to change the code, you could use the right click entry point, or the ribbon entry point on the home tab ... not quite the same, but hopefully it can help!
@@radsheets Thanks for creating this feature. I have the same issue as Lara. I want to force the user to input a date in a cell for a particular column. However, if the cell is more than 1 cell below a date, the icon does not appear. Where in the code do I go to change so that it looks for 5 above or 10 above? I did Alt + F11 and could not figure out how to adjust, mostly because I have no idea what the coding means and didn't know what to look for. Thanks!
Hey Jennifer! Thanks for commenting on it. I did a quick update to the date picker just now to check for a date number format in the cell ... so if you have a date format in the cell the grid icon should appear even if no dates are around it. There isn't a perfect way to check for a date format, so I did a more generic way that should work in most scenarios. The change is in function "checkForGridDP" which has logic to see if it should show the grid date picker icon based on the cell's number format. Let me know if it works!
@@radsheets Yes, it seems to be working great. Thanks so much.
Wow! wonderfull. Using the addin and works like a charm. Is there a way I can load the "addin" straight into my worksheet as computers at work dont allow addinns?
Hi Francois! You would have to copy / paste everything from the add-in into the workbook you want to use. Are you able to save the add-in to your desktop and then just dbl click to load it? Or is that blocked by your work too?
Sooh glad for your reply. all is blocked including that possibility. I tried to copy everything in and it gave errors. I realy would like to incorporate you module as it is very professional. Any way to send you a sample of my file. to see if you could assist? Maybe I did something wrong on the first try. it is realt one of the greatest date picker i have seen.
Thanks Francois! I added, to the FAQ section of the date picker download, a empty workbook with the date picker already copied into it. You can copy your items into that workbook and it should work well!
Thank you so much for all the effort. I have downloaded from the link and will give it a go today. very excited to have your datepicker in my workbook soon.
This is the best
Can this picker be used in a form?
Link to a version that can be in the description!
Hi! May I know why my workbook auto close while implementating the project. Thanks. I don't know if this is a bug or not.
Thanks - any idea how to make the calendar interactive so we can change the current month and select days say 2/3 months in the future? Is this a simple code amendment?
Many many thanks
Great video...is your website down?.Not able to download the file, also if you can publish on git it will be nice.
Hi Sam, I have updated the original download to your latest 221114 version and have a few questions. There was a small change in this version that causes a problem and wondered whether you can explain the reason for the change? It has to do with checking whether the selected cell is a date or not and supplements the original use of the IsDate function. You now check the number format string as well which seems irrelevant to me. Furthermore, if the number format is for a number, not a date, and includes the "[Red]" qualifier for example, the letter "d" incorrectly causes this version to treat the number as a date! My other question concerns worksheet protection; if the worksheet is protected the AddIn will not detect a selected cell with a date. I have overcome this in my own workbooks by enabling VBA to run using UserInterfaceOnly, but have you thought of a solution within the AddIn itself? Thanks again for such a handy tool!
Hi is it possible to use this to find a specific date on the sheer?
Hey Rad Sheets, this is awesome thank you for sharing. Just wondering, if I share the sheet with others, do they have access to use the date picker? or would they have to install the date picker too?
Thanks for this but unfortunately it's not working on Mac but the icon is there in the ribbon. How do I remove it?
Very helpful add
Hi, thanks for sharing, really great. is it also possible to add the datetime picker?
Cant use this with macros enabled.. do you need the macros?
Can you write a code for returning the selected "Checkbox names" to the desired cells?
I want to apply this in my Organization system can you help me for this
I'm new to Excel VBA. How to use it with 4 Textboxes on Userform? Help Please.
Hey. Many thanks for this great plug-in. One problem: Today is Friday 5.04.2024, Datepicler shows Saturday 05.04.2024. The wrong day of the week. Mysterious.
I have been using this amazing tool for several years in my VBA workbooks but for the last few months Excel has been crashing on me. Is there a known problem with the most recent Excel 365? The fault seems to be caused when switching worksheets after simply selecting random cells with the mouse on another worksheet. I'm using your date-picker as an Add-in.
Thanks for using the add-in! I haven’t experienced or heard of it doing that, though sometimes 365 does pick up a bug … though normally they get addressed pretty quickly like a week or so. You can try uninstalling the add-in and seeing if it continues to have issues. If you’re selecting random cells the add-in isn’t doing much so hopefully not the problem!
@@radsheets Thanks for the tip. I've updated to the latest download but same problem. I've also tried to debug the event handlers in my workbook as well as date-picker and all seems to be ok; the problem also cannot be replicated in breakpoint mode so perhaps it's a timing issue? Also, the problem cannot be replicated if date-picker is not installed! Do you have any suggestions for how I could debug this in case there's a VBA problem? I'd appreciate any further thoughts. Thank you.
Hey thanks for debugging! Sorry you’re seeing a problem! Based on your description I would try commenting out some of the API calls given those can cause Excel to crash, so the set timeout and mouse wheel detection to start and seeing if it still happens, and continue down that path of commenting out APIs … you could also try the date picker in the user form and see if that reacts, it’s similar code … sorry I can’t be of more help! I know these things can be a pain!
Will this work if i send my excel file to another user overseas? they likely will not be able to download anything into workstations
Does anyone know how I can change the VBA code so the calendar pops up automatically when a cell is clicked in? I have found that if the cells "number" is set to "date" it shows the Small Calendar icon to show the date picker. I would like for the date picker to open when the cell is clicked. I am pretty new to this but have enjoyed learning.
I've used your spreadsheet and copied it into my own so I've got the date picker working well now, except I can't scroll the month and need to change it to dd/mm/yy format. How can I do those things?
Thanks Tess! For the dd/mm/yy, the function GetDatePicked() returns the date to enter, and it pulls from datePicked which is the string of the date, so adjusting that inside that function to dd/mm/yy is a quick way. That all gets set from the tag on the labels, so changing the tags to be dd/mm/yy would work as well.
For the month changing, clicking on the month name will let you pick another, if you want mouse wheel then you'd have to adjust the 64-bit api's in the api_scroll_wheel file to work (last I tried I was hitting some strange errors, but left the code there in case someone wants to adjust it).
@@radsheets thank you! 😊
HI Rad, I am battling to make the date picker work on my latest MS 365 software, although i have 'unblocked it as well, as recomended by you. Is there anything else that i need to do,. pls confirm
Thanks
Martin ( south africa)
Hello Rad!
I want to know if we can change the daystart of the week.
Your week starts on Sunday but mine on Monday :')
Can we make this little change that hurts me a bit
Hey Rabapt! Thanks for the comment! I talk about that around 15:25, basically adjusting the startOfMonth variable to say the first day of week is Monday. You'd also have to update the top labels on the calendar, but totally do-able!
Hi, I hope you may be able to help me. First I must say I love this calendar for excel. I use lots of Excel docs for work and having this calendar has been very useful. I also use mostly a mac but recently had to purchase a laptop. Now I am using both. When creating my excel I came across your datepicker calendar and began using it on my laptop and for the first few days, I was able to use iCloud and go back and forth working on the excel entering all my data without any issues regardless if I was updating the excel on my pc or Mac. However, yesterday there was an update for excel on my mac and today I noticed my datepicker has an error and I keep getting a message to debug it. I have no idea how to do that. Are you able to help me or share a link on how I may be able to do so? Thank you in advance for your assistance.
Great work!!
How to add weeknumbers?
Hello, I went thought your video example and tried to add the functionality in your Add-In (specifically month/year picker and month up/down) but I noticed that the basic implementation of both is different and was not able to add these to the code shown in the video. What I need is a picker that can have this functionality but also is specific to one (or two) selected cell(s). I was able to make some progress reverse coding some of your Add-In onto the example you show in the video but have had some issues I cannot find a way around. Namely, getting the calendar to auto-populate at the start with the current date and passing the picked date to the cell where the calendar picker lives (I can make the spreadsheet available if you want to see it). If you could post how to do this that would be of great help. Thank you in advanced.
Hi DuHast! Based on another comment I adjusted the date picker to show on pre-formatted date cells, does that help with your spreadsheet? So on the cells you want the date picker to appear, just format the cell as a date format and it will appear.
@@radsheets Hello Rad, thank you for the reply. The spreadsheet where I want to use the picker has many calculated dates that stem from the one specified in the input cell (where the date picker will live). I'm trying to make it work in a test spreadsheet first as to not have the new code become confused with the code currently in the spreadsheet.
I am so close to making it work. On one hand, the calendar is not automatically populated and gives an error (invalid procedure call or argument), once the error is ignored and the month and year are picked by clicking them, then the calendar populates with the right dates. But, when clicking on a day, the date is only passed along to the cell if it is within the first 23 days displayed in the calendar.
On the other hand, I can make the calendar auto populate nicely, and the month and year become unclickable as well as the days displayed, so no date is passed along to the cell. So this is the dilemma, I can make some of the features work but others break... I'm sure if you could take a look at the code you could figure it out quickly. If you want to take a look at the spreadsheet let me know and I can send it over.
Hey DuHast! If you upload it somewhere I can download and take a look. I did just upload, in the FAQ of the date picker, a download link for a blank workbook with the date picker copied into it and working. So you could start with that and add your sheets / data to it and it should work well!
@@radsheets Cool, I can make the spreadsheets available in Dropbox, do you have a public email address I can send you the shared folder info to?
Nice. The symbol is printed. Can this be prevented?
Hi Sir, i want to change the ribbon or better, i want to rename it into the my native language. I dont find the code in the add in to actual do this. Can u help me?
That so amazing !
Your add-in is very helpful for me.
But it will be great to me if you can change the selection by scrolling to change the time when clicking on the hour or minute like choosing the month like now
Meaning that:
When clicking on the month or year, it is still selected as it is in your current project.
And when clicking on a date, it will select a date, and it will save that selected date.
Then click on the hour or minute to select the time with scrolling the mouse wheel and select the Day - month - year - hour minute to save it down to the cell
Can you help me on that?
Does it work in excel for Mac?
Very good Plug-in ❤❤❤👍
my question is can I change the calendar to start on Monday, not Sunday? I would like to use the one where it starts Monday first not Sunday, in which programming line is it possible to change this? Thank you very much for the answer.
Hi @radoslavstuchlak8296, You need to change the following line in the module 'dp_core' in the sub populateWeekdayNames().
Original for Sunday:
datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbSunday)
New for Monday:
datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbMonday)
##########
Also change the following line in the module 'dp_core' in the sub populateDatePickerDays().
After the comment line:
'get the Day of the week for the start
Orginal for Sunday:
iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbSunday)
New for Monday:
iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbMonday)
##########
The following still needs to be changed:
In the form 'datepickerform' in the procedure - 'Private Sub UserForm_Initialize()'
After the comment line
'set the current date and time
Orginal for Sunday:
todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbSunday)) & ", " _
& VBA.MonthName(VBA.Month(VBA.Date)) & " " _
& VBA.day(VBA.Date) & ", " _
& VBA.Year(VBA.Date)
timeButton.Caption = VBA.Time
New for Monday:
todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbMonday)) & ", " _
& VBA.MonthName(VBA.Month(VBA.Date)) & " " _
& VBA.day(VBA.Date) & ", " _
& VBA.Year(VBA.Date)
timeButton.Caption = VBA.Time
##########
I have tested and used it, it works great!
Wondefull. How can I add error handler to not allow someone to pick date on a protected cell. Currently if they do, I get an error and wants to debug.
Updated the downloads to error with cell protection! Thanks for the feedback!
@@radsheets Tested and works great with ammended code. Thanks. Can I copy only one sub that had changes into my worksheet, or must I go and put everything in again. If only one..Which one must I replace?
Hi, what could have been the problem if 1st to 12th day is mm-dd, while 13th to last day is dd-mm ?
after a few weeks of using the widget, the small icon showing the date on the worksheet turned to a blue square and is not active anymore, how can i fix it?
I like this date picker but I don't like that it displays the calendar prompt icon on the sheet for cells that are formatted as "currency" instead of just cells that are formatted as "date."
It doesn't do this for cells formatted as "general" or "text" or "percentage."
Any fix for this?
? visual Basic for application. Run-time error "53": File not found user 32 ( and on the last right button = debug). I am using mac27
hi, i have a 64bit edition of excel and really appreciate this video - i was following it all until we got to the MoveFormToMouse line, as soon as i add that, VBA highlights the ShowCal Sub line in yellow and throws an error.. where am i going wrong?
also im a bit confused.. it seems after i import the add-in and put it in the xstart folder, when i click alt+f11, the datepickerform is already there, along with the api's and everything else
so what is the userform1 for? is that the same thing? do i need to create a userform1 as well as having the 'datepickerform'?
i dragged the date picker form over to the project with my current worksheet, and did the same for the other folders class and module.. now i think ive made a bit of a mess and have no idea if i should wipe the lot and start again, whether or not i should import the addin and use that or not bother with that and instead recreate a userform1 as you did in the first 8 mins or so of the video?
sorry.. new to excel but really wanted a drop down date picker.. i dont know why Microsoft dont have one for the 64 bit edition and why they havnt bothered to make one - literally spent days just trying to create a drop down box when it couldve been done with a few clicks :(
anyway, ive subbed because youre the only person so far whos even bothered addressing the issue for a 64bit edition
any help would be greatly appreciated though - if you have a video which just shows what to do with the downloaded file, how to add the add in and use the vba stuff you already have within the downloaded files and what to drag where to get it to work, id really appreciate that, hopefully save me typing lots of code as i think that is really throwing me off - im bad enough with excel let alone coding on top of that :)
many thanks
i managed to make sense of it, i used the FAQ as suggested by @francoismarsau6412 in one of the comments and found blank workbook i could open at the same time as my worksheet and drag the module/class etc over
really happy with that, spent nearly a week of 5 or 6 hours a day to get my timesheet template done - you should definitely have way more subs that you have currently
absolutely brilliant!! thank you thank you thank you thank you!!!
how can I link this code to a range of cells and eliminate the button (one less click)
How did you get the small “14” picture in excel as a code?
Great work! Is there any way that it could be made to start the week on Monday, instead of Sunday?
Hi @tommulcahy2598, You need to change the following line in the module 'dp_core' in the sub populateWeekdayNames().
Original for Sunday:
datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbSunday)
New for Monday:
datepickerform.Controls("dayofweek" & i).Caption = VBA.WeekdayName(i, True, vbMonday)
##########
Also change the following line in the module 'dp_core' in the sub populateDatePickerDays().
After the comment line:
'get the Day of the week for the start
Orginal for Sunday:
iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbSunday)
New for Monday:
iStartOfMonthDay = VBA.Weekday(the_startOfMonth, vbMonday)
##########
The following still needs to be changed:
In the form 'datepickerform' in the procedure - 'Private Sub UserForm_Initialize()'
After the comment line
'set the current date and time
Orginal for Sunday:
todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbSunday)) & ", " _
& VBA.MonthName(VBA.Month(VBA.Date)) & " " _
& VBA.day(VBA.Date) & ", " _
& VBA.Year(VBA.Date)
timeButton.Caption = VBA.Time
New for Monday:
todayButton.Caption = VBA.WeekdayName(VBA.Weekday(VBA.Date, vbMonday)) & ", " _
& VBA.MonthName(VBA.Month(VBA.Date)) & " " _
& VBA.day(VBA.Date) & ", " _
& VBA.Year(VBA.Date)
timeButton.Caption = VBA.Time
##########
I have tested and used it, it works great!
I have set te date picker as addin but when i close excel and restart it wont show up at the start ribbon
It started showing values such as 9.5 as a date (9th of September) and I can't find a way to turn it off :( It's never done that before
Hi do you have file for the module?
Hi , i would like to know to add datepicker in userform . please help me out ,i am new to vba
Hey Mohd, great question: its worth a video on its own so I'll put in in the queue, but you would have a couple ways to do it
1-build the form and reserve part of it for a date picker, and show that part so its all in one place
2-from a button on the form open up another form, which would be the date picker.
Either way you can start with the workbook in the video (its in the FAQ download on the site) and decide if you want expand that form or pop it up. I'll be sure to do a future video on date picker popups in user forms! Thank you for the idea!
Thanks for the video buddy excellent UI YOU HAVE CREATED
Hi Mohd! Here's the detailed video and sample spreadsheet with it, thanks for the comment! ua-cam.com/video/pbalCb07ECw/v-deo.html
Thanks a ton bro 👏👏
Can you change the icon so its not as red.
Day shown in month The month is displayed as a date. How to fix it? , thanks in advance
my computer set date is "dd/mm/yyyy " i'm live in asia
1 little bug, whenever I click the first row or any column, I get a run-time error '1004'
hi. this is not working in Mac
It shows runtime error '53' on my mac
Ah yeah, I don't think the Mac can do the API part of the code given its Windows specific, but the other parts should work. If you remove the APIs and calls to them, should run.
@@radsheets I have the same problem! Any help on how to do this? Remove the API?
Date Picker itself does not run on MAC. The first shown error is: "Run-time error '53': File not found: user32"
stop using Mac, and buy a Pc with windows
Everything worked perfectly until I reached the last part where clicking on a date is supposed to fill in the cell automatically. It is not working😭 and I wrote all the codes similarly one by one as in this video. Plz help!!!! 😭😭😭
You should finish the tutorial.
wow
I have tried the blank workbook template on 5 computers and double checked the macro settings and unblocked the file. pop up icon not working. can I supply my email adress to contact you with a sample?
can I supply my email adress to contact you with a sample?
@@francoismarsau6412 Hey Francois, in the about section there is a email address.
@@radsheets Thank you
@@radsheets Thank you Have sent email
@@radsheets Thank you. hve sent email