Home
Creators
Search Recent Random
Posts
Search DMs Popular Hash Lookup Tags Random
Importer
Import FAQ
Register Favorites Keys Review DMs Logout
Community
Telegram ThePornDude
Home Creators Posts Import Register Favorites Logout

Date Picker Add-In Update [Gold & Silver] (Patreon)

Published:
2024-05-20 12:15:01
Edited:
2024-05-23 02:21:17
Imported:
2026-09

Downloads

Content

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.

Files

Comments

Malcolm Sokol

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?

Malcolm Sokol

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.

Excel For Freelancers

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.

Gerrie Pretorius

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

Gerrie Pretorius

Good Day Randy, I found that when you use the Custom Nr like 0.00"m" Then the Date picker also activate.

Excel For Freelancers

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.

Excel For Freelancers

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.

Malcolm Sokol

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.

Excel For Freelancers

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

Malcolm Sokol

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.

Wim Chrispeels

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.

Excel For Freelancers

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.

Excel For Freelancers

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.

Zeshaan

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?

Excel For Freelancers

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.

Malcolm Sokol

Hi Randy. have taken the data picker code and resources and moved them into my applicaton as a regular form and not part of an add in. Just makes it easier to use an not have to worry about making it active etc. Similar to your old date picker where the itmes were copied to the applciation. But I have a problme. It seems the data picker sheet 1 with all the settings is protected and I need to make some small modifications to this sheet. For example the holiday date format for cell F2 is used to set the inherited date format when there is a user form initiation as there is no cell to reference. This is set to the default long date with * which omits leading 0 on single digit months and days. I cannot change the format. So is there a password I can enter to clear the format as these cells are locked and I assume there is a password somewhere to unprotect the sheet. Thanks.

Excel For Freelancers

Hi Malcolm, thanks for your message and I am glad you like the date picker, There is no password on these files for sure (Only applications that I sell contain passwords) For the Addin, the sheets are automatically hidden. In the VBA Editor when you go to workbook properties and look for 'is Addin', when you change this to false, then the sheets will appear. I hope this helps and thanks so much.

Malcolm Sokol

I think I was interpreting the fact that when I went to unlock the sheet the behavior seemed to look like there was a password. To make sure I used vba to report the protections on all sheets and the workbook and there was none applied. But please see my post in the Excel for Freelances facebook group about the fact that VBA does not return mm/dd/yyyy as the number format text when the cell is formatted that way. It returns m/d/yyyy. Of course the worksheet displays the formtting as set but VBA does not pick this up. And when trying to get it to work, I was perplexed as to why the code always returned m/d/yyyy even after I applied a custom format to the cell as mm/dd/yyyy. So I just assumed as well that the sheet was protected wand was not accepting the change. Then I learned that VBA is not capable of picking up the format string as intended. This issue was flagged in a normal web query and with Chat GPT as well. So to get my user form initiated date picker to do what I wanted I hard coded the format string. BTW I also added an option to my user form control with the date text, to clear the text (set it to "") when I hold down the Shift key on a mouse down event. This allows the box to be cleared and the date picker trigger user form to not be called.

Excel For Freelancers

Hi Malcolm i have not added any passwords to these templates. If this is your own sheet, then there may be a password that was set, since my templates don't contain passwords. In VBA you can change any date format via YourDate = Format(YourDate,"yourformat") I hope this helps and thanks so much.

Zacrux

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 ?

Excel For Freelancers

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.