Date Picker Add-In Update [Gold & Silver]
In this week’s Feature, Fix, or Focus, we take last week’s Date Picker Add-In training.
Here's the list of updates I made for this training:
· Colored Holidays requested by Gerrie & Peter
· Date Picker In Userform requested by @prabhatrawat4394 @danp6101
· Added Chinese Characters/Translation requested by Malcolm
· Fixed Date Formatting Issues added by Randy Austin
· Added Toolbar Weekday Languages added by Randy Austin
· Added Date Picker Months Languages added by Randy Austin
Please don’t forget to add your requests for your own Feature, Fix or Focus each week when I post a new video, right here on Patreon. Each week I will either add a new feature, fix a bug or an issue, or create another training with a special focus on any part, all based on your feedback and comments.
Comments (17)
So in order to get the datepicker to show on a user form is add Useform_ShowDatePicker to the TextBox Enter event ? I can't get it to work.. Is there a way to use the Date text from the userform.TextBoxDate control as the start date ?
Hi and thanks for your message. You want to make sure to use the right field control Enter event and also you want to make sure each userform contains the macro to open the date picker. For example in the Holiday userform we have the following: Private Sub HolDate_Enter() Useform_ShowDatePicker End Sub Sub Useform_ShowDatePicker() Dim LeftPos As Double, TopPos As Double, Xpos As Double, YPos As Double 'Form Positioning Code Found here: https://stackoverflow.com/questions/6116929/align-userform-to-cells-in-excel# Xpos = GetDeviceCaps(GetDC(0), 88) / 72 'Set Leftmost Horizontal position YPos = GetDeviceCaps(GetDC(0), 90) / 72 'Set Upper Vertical Screen Position LeftPos = Me.Left + ActiveControl.Left TopPos = Me.Top + ActiveControl.Top + Me.ActiveControl.Height + 22 CalendarFrm.Left = LeftPos '(ActiveWindow.PointsToScreenPixelsX(LeftPos * Xpos) * 1 / Xpos) 'Set Forms Left Position CalendarFrm.Top = TopPos 'Set Forms Top Position under field CalendarFrm.Show End Sub I hope this helps and thanks so much.
Hoping someone might be able to assist. I want to add the datepicker to a form from the Vehicle Expense Pro vehicle userform. What should I do to accomplish that?
Hi and thanks very much. I show you how to do that in this training here: https://youtu.be/1JtbErLgOnA If this is something you would like help with customizing this template, I would be happy to refer a qualified developer for a fair price. Just let me know and thanks. Please email me the details and your budget. Randy@ExcelForFreelancers.com I hope this helps and thanks so much.
Where can I find the sheet with the written days and months. There is an error appearing in the Dutch & French translation. May in Dutch is 'mei' and in French it's 'mai'. Thank you.
HI and thanks that is on Sheet1 of the add-in. Make sure you go to the 'Workbook' in the code and in the Properties change 'Is Add-in' to False instead of true. Make the changes you need, Change the workbook back to -Add-in = True then save the workbook. (I do cover this in the original training so please make sure to watch the full training) I hope this helps and thanks so much.
Also learned a lot about the AddIN system and how to turn on add ins if not on (but available) and to remove them from the application if they are enabled. So for the Date Picker, can use code to remove or add to ribbon. Also there is code to add a routine from a source file if it has never been added, hence unavailable to activate. I believe this code would also allow you to update the file referenced by a previous install but have not tested this out. Really interesting functionality in Excel.
Yes thanks so much. I have left that code in there in case you want to make some updates. I hope this helps and thanks so much.
I have update the code to test the B1 cell for the escape value whatever it is before testing within the found range code where it was done after testing date format. Since the macro runs on any selection change in the entire application, seemed like a better idea to get out of the routine before any complications with dates etc. since there were no changes coming anyway. Would be nice to be able to have a class macro for only a given worksheet but that would be hard in an add in since there would be no knowledge of application workbook structure so doing it application wide is likely the only way I assume.
HI Malcolm, very interesting and its great that you are testing out different options and features. That is the best way to learn and to optimize any application. Very well done
Good Day Randy, I found that when you use the Custom Nr like 0.00"m" Then the Date picker also activate.
HI Gerrie, thanks for the details. You may want to add an additional exit sub line for additional formats as i have done with the the one in which the date picker displays on the currency within the selection change event. I did go over that in the updated training video so please feel free to add additional lines of code to meet your requirements. I hope this helps and thanks so much.
Morning Randy, a last request on the datepicker would be to have a button that will take you to today's date when you click on a cell which already have an older date in it and you would like to update it. I don't know if this would be possible
HI Gerrie, thanks for your comment. The Date Picker is designed to display the current year and month when no date is displayed in the cell or the selected month and year if the cell contains a date. I did go over this in the original training so please feel free to customize this if you want some different functionality. I hope this helps and thanks so much.
Thinking some more about the add in. Am I correct that if an add in is added using the add in management tools it becomes a feature of the Excel app on the computer whether you want to use it an any other files. I certainly don't want all my workbooks to use the date picker on any date formatted cell. Even though there is a turn off feature on the menu bar, that can do this, I don't need to have these features visible on the menu bar when not needed. And I don't want to have to modify my screen even if I can remove them from the ribbon using ribbon management tools. So maybe a programmatic approach to loading the add in when it is desired to be used in a specific workbook so it does not become a universal feature of my Excel installation.
Hi Malcolm, the ribbon controls are packed with the add-in, so whatever machine the add-in is installed is where the datepicker and custom ribbon will appear. If you follow the training, you can customize this date-picker and the toolbar however you like. There is a default add-in location however you can set any location you want to for the add-in. I hope this helps and thanks so much.
Question about moving to a new computer. Say I have an application that uses the date picker add in. That is a separate file. I also have to support the Ribbon Controls and there is a methodology to build the XML. So what do I need to copy to a new computer or include in a distribution for others to use and what is included in my application after I build it from all the add ins etc. I suspect that on a new computer I will need to add the Add IN to available add ins. Also is there a default location for add ins that is checked without having to specify a specific add in folder location when configuring the new add in in Excel?