I've written about using calendars/date pickers in userforms before but this one is particularly easy and flexible to use, even direct from a worksheet.
Thanks to our resident Excel guru Catalin for putting me on to it. It was developed by Trevor Eyre and you can read more about it here. You can download my workbook (at the bottom of the page) with working sample code for you to use and adapt.
Multi-Language Support
I've modified the original code so that the calendar can now display the months and days in English, French, Spanish, German, Portuguese and Italian.
Thanks to Jorge Cabral for the Portuguese translation.
If you need another language, let me know in the comments below and I'll see if I can add it for you.
This date picker is implemented as a userform. All the code that makes it work is part of the form itself. No separate modules to worry about.
Using it is very simple. Add/import the CalendarForm form to your workbook, or make a copy of the workbook with the CalendarForm in it. Then call the form's GetDate function. This function returns the date you select.
There are a number of customizations you can make within the function call. For example you could change the first day of the week. The default is Sunday, but in my code I'm making it Monday
By default, months and days are in English, but you can also use French (fr), Spanish (es) or German (de) by specifying the language using the 2 character language code e.g. for Spanish
You can change the color that Saturday and Sunday dates are shown in by specifying RGB values
Check out the GetDate function in the CalendarForm module to see the other customizations you can make.
Using the Date Picker in a Userform
You might want to call the date picker from another userform. I've put together something very basic which is loaded by clicking a shape in the worksheet.
Clicking on the 'Choose Date' command button calls the CalendarForm.GetDate function which displays the date picker
You can call the GetDate function from any form element that you click on, it doesn't have to be a button.
Clicking on the month lets you choose the month. Clicking on the year lets you choose the year. Or you can use the scroll buttons to move backwards and forwards through the months.
When you click the date, that date is what the GetDate function returns and you can do with it what you wish. I've written code to put the date into a label on the form, a text box, and into cell A1 on the current sheet.
By placing the chosen date into a label, the date is not editable so you are forcing your users to use the date picker.
Anything in the text box is editable, so can be changed by your users, and this may mess up what you are trying to do.
The number of years displayed in the calendar is configurable. For example by specifying RangeOfYears:=2 when you call GetDate, the calendar will show 2 years either side of the current year. So in 2020, you'll be able to pick years from 2018 through to 2022.
NOTE: VBA handles dates in US format so I need to use the Format function to display the date the way I want, dd/mm/yyyy. You may have to do something similar if you don't use mm/dd/yyyy
Using the Date Picker From a Worksheet
You can call the date picker from the worksheet without needing to create another userform.
Clicking On An Object
Insert something on your sheet that you can assign a macro to, like a shape or an icon. Write a macro to call GetDate and assign that macro to your shape, icon or whatever it is you are using.
The following code is run by clicking a shape on a worksheet. The selected date is entered into cell A12.
Clicking on a Cell
By using a worksheet event you can display the calendar just by clicking in a cell (or a range of cells).
The VBA uses Intersect to check if the cell clicked is within the TriggerCells range, which is whatever range you wish to define. In my code I'm just using C29.
Thanks to Dan McGovern for suggesting and supplying this code.
Examples of all of these are included in the workbook you can download.
Download the Workbook and Calendar
Enter your email address below to download the sample workbook containing the code in this post.
Related Articles
Excel Forms Create a simple user form [VIDEO]
Excel Form Data Validation How to check the data being entered in your form is correct, and communicating with the user about errors
Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.
Searching for Data With a User Form. Create a userform to search for data on the worksheet.
Hide, Delete and Sort Worksheets. Conveniently manage a large number of worksheets with a userform.
Add Data to Combo Box Drop Down List in Excel Form. Dynamically add elements to your combo box lists.
Automating and Emailing Pivot Table Reports
jcabral
Hi Philip Treacy / Catalin Bombea~
I would like to incorporate public holidays into the calendar.
Is it possible to do this from a range that is on a certain sheet?
Can you give some tips on how to do this?
Thank you very much
Jorge Cabral
Catalin Bombea
Hi Jorge,
What should the calendar do with the holidays, not clear what holidays incorporation means?
jcabral
Hi Catalin
If you have a list of holidays, on a certain sheet, then on the calendar that day appears in a certain color.
Catalin Bombea
Hi,
The calendar does not have this functionality, unfortunately, so it needs to be customized.
Try opening a new topic in our forum, maybe one of our members can help you.
Catalin
Jorge Cabral
Hi Catalin
I want to say that I have a list of holidays, in an Excel sheet, that I would like to see on the calendar.
Who says holidays, they could be a set of important dates that I want to see highlighted on the calendar. In other words, you would have to put a range in the calendar where these dates are.
I don’t know if I was clear enough?
MD
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
‘ Thanks to Dan McGovern for suggesting and supplying this code
Dim TriggerCells As Range
‘ The cells you click in to display the calendar
Set TriggerCells = Range(“C11”)
If Not Intersect(TriggerCells, Target) Is Nothing Then
Range(“C11”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
End Sub
hey thank you for making it
Every time I don’t pick any date it shows 00-Jan. I want it to show today date for example or show the date I picked before.
Catalin Bombea
Best practice is to store the date in a variable, in order to test it:
Dim Dt as Date
Dt=CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
If Clng(Dt)=0 Then Dt=Date ‘
Range(“C11”).Value=Dt
Or, if you want to keep whatever is already in the cell, if a date was not selected:
If not Clng(Dt)=0 Then Range(“C11”).Value=Dt
MD
Hey thank you for taking your time to help.
how my code looks right now:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Dt As Date
Dim TriggerCells1 As Range
Dim TriggerCells2 As Range
Dim TriggerCells3 As Range
Dim TriggerCells4 As Range
Dim TriggerCells5 As Range
Dim TriggerCells6 As Range
Dim TriggerCells7 As Range
Dim TriggerCells8 As Range
Set TriggerCells1 = Range(“C11”)
Set TriggerCells2 = Range(“F11”)
Set TriggerCells3 = Range(“I11”)
Set TriggerCells4 = Range(“L11”)
Set TriggerCells5 = Range(“C20”)
Set TriggerCells6 = Range(“F20”)
Set TriggerCells7 = Range(“I20”)
Set TriggerCells8 = Range(“L20”)
If Not Intersect(TriggerCells1, Target) Is Nothing Then
Range(“C11”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells2, Target) Is Nothing Then
Range(“F11”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells3, Target) Is Nothing Then
Range(“I11”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells4, Target) Is Nothing Then
Range(“L11”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells5, Target) Is Nothing Then
Range(“C20”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells6, Target) Is Nothing Then
Range(“F20”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells7, Target) Is Nothing Then
Range(“I20”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
If Not Intersect(TriggerCells8, Target) Is Nothing Then
Range(“L20”).Value = CalendarForm.GetDate(FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
End If
End Sub
I just got in the vba sry. can you rewrite mine with yours?
Tried what you said did not work very well haha.
Catalin Bombea
Try this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Dt As Date
If Not Intersect(Target, Me.Range(“C11,F11,I11,L11,C20,F20,I20,L20”)) Is Nothing Then
Dt = CalendarForm.GetDate(FirstDayOfWeek:=vbMonday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
If Not CLng(Dt) = 0 Then Target.Value = Dt
End If
End Sub
MD
Thank you very much.
Exactly what I was looking for.
You saved many lines 😉
MD
One last thing. Is possible do deselect the cells after I pick the date I want? right now the selected cell stay selected. ty again
Catalin Bombea
You can’t deselect a cell, but you can select another cell.
After the line:
If Not CLng(Dt) = 0 Then Target.Value = Dt
Select another cell:
Cells(1,”A”).Select ‘this will select cell A1
Rémy
VERY nice date picker. Thank you so much for sharing. I had one which was way simple code wise and doing the job but yours looks great and got all the options I was lacking in mine (week numbers for example). Plus the code is nice and clean and I already learnt some stuff from it. Thanks again.
Cheers
Philip Treacy
Thanks Rémy. I didn’t create this code just added to it, but thanks for the kind words.
Regards
Phil
Rebecca
Thanks so much for this! If I want to use this date picker for two different dates is it sufficient to call the form and create a date variable based on the button clicked?
Do you see any potential issues with this method?
Private Sub imgFirstDate_Click()
FirstDate = CalendarForm.GetDate
If FirstDate 0 Then Me.txtFirstDate.value = FirstDate
End Sub
Private Sub imgSecondDate_Click()
SecondDate = CalendarForm.GetDate
If SecondDate 0 Then Me.txtSecondDate.value = SecondDate
End Sub
Philip Treacy
Hi Rebecca,
Yes that will work. Treat CalendarForm.GetDate like any other function.
You’ll need to change your IF though,your missing an = :
If FirstDate = 0
Regards
Phil
Aleem
Dear Sir,
Im looking for the excel calendar in Arabic Language, Ie for Hijri Calendar.
kindly share if you have Arabic Calendar in excel, or any advise how can we create the hirji calendar in excel
thanks
aleem
Philip Treacy
Hi Aleem,
Sorry we don’t have it in Arabic, but if you can make the translation for the days and months in this calendar wecna try to incorporate that.
Regards
Phil
Aleem
Dear Phil,
thank you for your reply. please see below the translation for the days and months, i tried changing and it got changed however the issue is the Arabic Calendar ( Hijri Calendar) will have only 355 days in a year and each month will have either 29 and the next month will have 30 days. Any month will never go to 31 days.
Im not sure how we can incorporate this.
Thanks
aleem
If Language = “AR” Then
Months.Add Item:=”Muharram”, Key:=”1″
Months.Add Item:=”Safar”, Key:=”2″
Months.Add Item:=”Rabee Awwal”, Key:=”3″
Months.Add Item:=”Rabee Thani”, Key:=”4″
Months.Add Item:=”Jumaada Awwal”, Key:=”5″
Months.Add Item:=”Jumaada Thani”, Key:=”6″
Months.Add Item:=”Rajab”, Key:=”7″
Months.Add Item:=”Shaban”, Key:=”8″
Months.Add Item:=”Ramadan”, Key:=”9″
Months.Add Item:=”Shawwaal”, Key:=”10″
Months.Add Item:=”Dhul Qadah”, Key:=”11″
Months.Add Item:=”Dul Hijah”, Key:=”12″
Days.Add Item:=”ah”, Key:=”1″
Days.Add Item:=”it”, Key:=”2″
Days.Add Item:=”th”, Key:=”3″
Days.Add Item:=”ar”, Key:=”4″
Days.Add Item:=”kh”, Key:=”5″
Days.Add Item:=”ju”, Key:=”6″
Days.Add Item:=”sa”, Key:=”7″
End If
Philip Treacy
Hi Aleem,
Modifying the code to have different numbers of days in each month will be very complicated. I didn’t write the original code so I wouldn’t know where to start with it. You could try contacting the original code author.
Regards
Phil
Koen STEVENS
@Ludo @Catalin
You really helped me.
Translation is now full functional.
Regards
Koen
Catalin Bombea
You’re welcome 🙂
Ludo Soete
Hi Koen,Catalin
Glad I could help you.
Made in my adapted version some additional translation like Swedish, Danish, Norwegion. Support now 10 different languages.
‘part of the adapted code:
‘using this enum, we can take advantage of the intellisense popping up
‘and select one of the defined languages.
‘if you want to add languages, dont forget to update this enum list.
‘with the exeption of English USA, are all the languages below sorted from A->Z
Public Enum calLanguage
English_USA = 0 ‘English USA = Default (due to the value = 0)
Dansk = 1 ‘Danish
Deutsche = 2 ‘German
English_UK = 3 ‘English United Kingdom
Español = 4 ‘Spanish
Français = 5 ‘French
Italiano = 6 ‘Italian
Nederlands = 7 ‘Dutch
Norsk = 8 ‘Norwegian
Português = 9 ‘Portuguese
Svenska = 10 ‘Swedish
End Enum
Note: The difference between English_USA & English_UK is the date notation.
The Week label, the Userform caption, button OK & Today caption is also translated to the appropriate selected language.
And the Date format is also depending on the selected language (dd/mmm/yyyy for European and mmm/dd/yyyy for the US date notation).
I’m willing to share my version, just don’t know how.
E-mailed them to the original writer of the code & to this website using the contact form, but never got a responce. 🙁
Best regards,
Ludo
Philip Treacy
Hi Ludo,
Thanks for this fantastic effort. If you want to either post a link to the file in a comment here, or open a Helpdesk ticket and attach the workbook to that, I’ll make sure it gets posted onto this page.
Regards
Phil
Oscar Garralda
Hi Ludo,
I did something similar which also fixed the known bugs and added additional features.
Most of the code has been rewritten with the aim of simplifying it and making it easier to understand (hopefully).
I would like to share it and keep improving it. Did you find a way to share it?
Best regards,
Oscar
Koen STEVENS
Hello, I already used your datepicker. Works perfectly.
Living in a multi-language country I love the new multi-language version.
I added Dutch (Nederlands, abbreviation NL)
If Language = “nl” Then
Months.Add Item:=”Januari”, Key:=”1″
Months.Add Item:=”Februari”, Key:=”2″
Months.Add Item:=”Maart”, Key:=”3″
Months.Add Item:=”April”, Key:=”4″
Months.Add Item:=”Mei”, Key:=”5″
Months.Add Item:=”Juni”, Key:=”6″
Months.Add Item:=”Juli”, Key:=”7″
Months.Add Item:=”Augustus”, Key:=”8″
Months.Add Item:=”September”, Key:=”9″
Months.Add Item:=”Oktober”, Key:=”10″
Months.Add Item:=”November”, Key:=”11″
Months.Add Item:=”December”, Key:=”12″
Days.Add Item:=”Zo”, Key:=”1″
Days.Add Item:=”Ma”, Key:=”2″
Days.Add Item:=”Di”, Key:=”3″
Days.Add Item:=”Wo”, Key:=”4″
Days.Add Item:=”Do”, Key:=”5″
Days.Add Item:=”Vr”, Key:=”6″
Days.Add Item:=”Za”, Key:=”7″
End If
To call the Dutch version you have to set
CalendarForm.Caption = “Kies een datum”
Is it possible to translate the abbreviation for week “Wk” and the word “Selection :”?
Regards
Love you regular mails.
Koen Stevens
Catalin Bombea
Hi Koen,
Where have you seen in code the “Wk” and “Selection” texts? I was not able to find such expressions.
See below Ludo’s comment, I guess this is what you meant.
Thanks for sharing
Koen STEVENS
I haven’t seen Wk and Selection in the code.
When the DatePicker is displayed Wk is the header of the column with the week numbers. “Selection : “ is the text that appears in front of the selected date.
In Dutch the translation of Wk is Wk and of Selection is “Keuze : “.
In French it would be Sm (from semaine) and “Sélection : “
I could not find in the code where these texts are initialised.
Catalin Bombea
The week number header has a label named lblWk. The label is initialized by bWeekNumbers parameter.
To initialize the calendar with week numbers, and with a custom week number label, you can use:
CalendarForm.lblWk.Caption = “Keuze:” (it should be a short name though, not that long)
CalendarForm.Caption = “Sélectionner une date”
myDate = CalendarForm.GetDate(FirstDayOfWeek:=Monday, ShowWeekNumbers:=True,…..
Catalin Bombea
Where do you see a text in from of the selected date?
““Selection : “ is the text that appears in front of the selected date.”
Ludo Soete
Hi Catalin & Koen,
As far is I can see, the WK label is hard coded and can’t be changed using the default parameters list. This is something you have to change in the userforms WK label. Anyhow, it’s an abreviation for ‘Week’. Luckely for us, we write Week (in English) the sameway in Dutch, so the abreviation WK will do it for me. I don’t know how the userform will behave if you place 4 characters in the WK label, and only 2 in the weekday names (Mo,Tu,..Su).
I added in my CalendarForm a few more parameters making it now also possible to enable / disable the Saturdays & Sundays. In some cases it could be interesting to disable the week-ends, preventing wrong selections.
As an extra, I can change the TodayDate’s font into Bold & Italic.
With the default small font size (9) it’s not clearly visible, but it can be done.
It becomes more clearly when you increase the DateFontSize to 16.
As last comment, I can let you know that I send this expanded CalendarForm to the original author of this great utility as to this website.
It’s up to those people to decide if they want to publish it or not.
p.s.:
@ Koen,
The impemented Dutch text in my version is all lowercase, example: januari, februari,ma,di,wo, ….
I notice that you use Propercase for those month & day names.
best regards from Belgium
Ludo Soete
Hi again,
@ Koen,
in addition to my previous answer from a few minutes ago – I’ve noticed your question to late – ,see below.
If you want to change the WK label text, as in your example for French, place an extra code line in the If Language = …
See example for French:
If Language = “fr” Then
Months.Add Item:=”Janvier”, Key:=”1″
Months.Add Item:=”Février”, Key:=”2″
Months.Add Item:=”Mars”, Key:=”3″
Months.Add Item:=”Avril”, Key:=”4″
Months.Add Item:=”Mai”, Key:=”5″
Months.Add Item:=”Juin”, Key:=”6″
Months.Add Item:=”Juillet”, Key:=”7″
Months.Add Item:=”Août”, Key:=”8″
Months.Add Item:=”Septembre”, Key:=”9″
Months.Add Item:=”Octobre”, Key:=”10″
Months.Add Item:=”Novembre”, Key:=”11″
Months.Add Item:=”Decembre”, Key:=”12″
Days.Add Item:=”Di”, Key:=”1″
Days.Add Item:=”Lu”, Key:=”2″
Days.Add Item:=”Ma”, Key:=”3″
Days.Add Item:=”Me”, Key:=”4″
Days.Add Item:=”Je”, Key:=”5″
Days.Add Item:=”Ve”, Key:=”6″
Days.Add Item:=”Sa”, Key:=”7″
lblWk.Caption = “Sm” ‘<<< add this code line
End If
This means you dont have to mess into the Wk label. :), and can easily be adapted for the supported languages.
I dont know the week abreviations for the other supported languages, but the above added code line solves your problem for French and serves as an example on how to do for the other languages .
Best regards,
Ludo
Koen STEVENS
Hi Catalin,
Above the buttons “Today” and “Okay” there us a line with the text
“Selection : ” followed by the selected date.
I would like to translate this too.
Regards
Catalin Bombea
Ah, ok, I see now, you are using the options: TodayButton:=True, OkayButton:=True to call the calendar.
Those 2 are command buttons, they have a hard typed value, but you can change it in runtime, just like you did with week label:
CalendarForm.cmdToday.Caption=”Today”
CalendarForm.cmdOkay.Caption=”Okay”
CalendarForm.lblSelection.Caption=”Selection:”
Ludo
Hi,
I added the Dutch language to my copy of the Date Picker.
here’s the code i added:
in module1
‘
Sub ShowCalNl()
‘ Dutch
Dim myDate As Date
CalendarForm.Caption = “Selecteer de datum”
myDate = CalendarForm.GetDate(Language:=”nl”, FirstDayOfWeek:=Monday, SaturdayFontColor:=RGB(250, 0, 0), SundayFontColor:=RGB(250, 0, 0))
If myDate > 0 Then Range(“I28”).Value = myDate
End Sub
in the Calendarform:
‘
If Language = “nl” Then
Months.Add Item:=”januari”, Key:=”1″
Months.Add Item:=”februari”, Key:=”2″
Months.Add Item:=”maart”, Key:=”3″
Months.Add Item:=”april”, Key:=”4″
Months.Add Item:=”mei”, Key:=”5″
Months.Add Item:=”juni”, Key:=”6″
Months.Add Item:=”juli”, Key:=”7″
Months.Add Item:=”augustus”, Key:=”8″
Months.Add Item:=”september”, Key:=”9″
Months.Add Item:=”oktober”, Key:=”10″
Months.Add Item:=”november”, Key:=”11″
Months.Add Item:=”december”, Key:=”12″
Days.Add Item:=”zo”, Key:=”1″
Days.Add Item:=”ma”, Key:=”2″
Days.Add Item:=”di”, Key:=”3″
Days.Add Item:=”wo”, Key:=”4″
Days.Add Item:=”do”, Key:=”5″
Days.Add Item:=”vr”, Key:=”6″
Days.Add Item:=”za”, Key:=”7″
End If
i hope I didn’t forgot something.
kind regards from Belgium.
Catalin Bombea
Thanks for sharing 🙂
Leslie Glasser
Date Picker is a marvellous facility. As a challenge (and I cannot assist with the languages), how about Arabic or Hebrew? Even the numbers would not be the standard Hindu-Arabic 0, 1, 2, …
Philip Treacy
Hi Leslie,
I don’t speak Arabic or Hebrew either but I’m happy to make the necessary changes and help anyone who does.
Phil
Alex
Just wanted to say thank you for making this happen. Looks really awesome!
Philip Treacy
You’re welcome 🙂
JCabral
Is it possible to put the months and days of the week in another language, or is it necessary to make major changes to the code?
Catalin Bombea
Hi,
Check the code, there is a section with the following code, change it to your preference, should work.
‘Only minimum date occurs in selected year
ElseIf YearIn = YearMinDate Then
For i = MonthMinDate To 12
cmbMonth.AddItem Choose(i, “January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)
Next i
If MonthIn < MonthMinDate Then MonthIn = MonthMinDate cmbMonth.ListIndex = MonthIn - MonthMinDate 'Only maximum date occurs in selected year ElseIf YearIn = YearMaxDate Then For i = 1 To MonthMaxDate cmbMonth.AddItem Choose(i, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") Next i If MonthIn > MonthMaxDate Then MonthIn = MonthMaxDate
cmbMonth.ListIndex = MonthIn – 1
‘No minimum or maximum date in selected year. Add all months to combobox
Else
cmbMonth.List = Array(“January”, “February”, “March”, “April”, “May”, “June”, “July”, “August”, “September”, “October”, “November”, “December”)
cmbMonth.ListIndex = MonthIn – 1
End If
JCabral
Thanks, work perfect.
Many thanks
Stay Safe, Stay Home
Philip Treacy
Hi,
I’ve modified the Calendar so that you can use it with French, Spanish and German as well as English. Did you want to use another language?
You can download the updated workbook from the blog post
Regards
Phil
JCabral
Hi PhilipTreacy
My small contribution to the sixth most spoken language in the world, Portuguese.
If Language = “Pt” Then
Months.Add Item:=”Janeiro”, Key:=”1″
Months.Add Item:=”Fevereiro”, Key:=”2″
Months.Add Item:=”Março”, Key:=”3″
Months.Add Item:=”Abril”, Key:=”4″
Months.Add Item:=”Maio”, Key:=”5″
Months.Add Item:=”Junho”, Key:=”6″
Months.Add Item:=”Julho”, Key:=”7″
Months.Add Item:=”Agosto”, Key:=”8″
Months.Add Item:=”Setembro”, Key:=”9″
Months.Add Item:=”Outubro”, Key:=”10″
Months.Add Item:=”Novembro”, Key:=”11″
Months.Add Item:=”Dezembro”, Key:=”12″
Days.Add Item:=”D”, Key:=”1″
Days.Add Item:=”S”, Key:=”2″
Days.Add Item:=”T”, Key:=”3″
Days.Add Item:=”Q”, Key:=”4″
Days.Add Item:=”Q”, Key:=”5″
Days.Add Item:=”S”, Key:=”6″
Days.Add Item:=”S”, Key:=”7″
End If
Jorge Cabral
Stay safe, stay at home
Philip Treacy
Thanks Jorge, I’ve incorporated this into the workbook.
Regards
Phil
Mark Baxter
Hi Phil,
Thanks for this it is really useful. Is there any way to update the code so that the form pops up next to the cell you want to input the date in? Currently it always shows in the centre of the screen.
Thanks
Bax
Philip Treacy
Hi Bax,
Yes I think you could do this but it’s a load of hassle. To set the startup position you need to know things like the monitor size/resolution and you’d need to know where the cell is (x,y co-ordinates in relation to the screen) to position the form as you wish.
With so many different screen sizes to account for, it’s not straight forward. I’ve never done it.
https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/startupposition-property
Cheers
Phil
Nick
Off-topic a bit – I went through your old posts and didn’t see anything on this.
Could you tell me what colors/font you’re using in the VBE? Maybe do a blog post on your settings?
Philip Treacy
Hi Nick,
I’m using VS Code as my editor – hence the colors. It has a load of useful features and I use it for everything I do these days. HTML, CSS, JavaScript and PHP.
Cheers
Phil