Activate userform in userform

Solved/Closed
Report
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
Hello,

I've just started exploring the possibilities of userforms.

My userform contains a TextBox named "txtAanvraagDat". In this textbox a date must be entered. So I thought it would be nice if a calender would pop up.
I created another userform containing a calender, but how do I make the calender appear when the textbox "txtAanvraagDat" is selected?

Please let me know if more info is needed, because I really want this to work.

Best regards,
Trowa

5 replies

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hello Trowa,

Hope this new file will match your request : https://authentification.site/files/19417930/PostPlanning_4.xls

Best regards
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Hi Aquarelle,

My request has been matched, thanks for your time and see you soon.

Best regards,
Trowa
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489 > Trowa
Hi,

It is always a pleasure to help you when it is possible for me :) See you soon.

Best regards
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hello Trowa,

Is it possible for you to upload an example of your file (without personal data) and to put the link in your next message ?

Best regards
Hi Aquarelle,

Nice to see again.

Here is my file:
https://authentification.site/files/19337862/PostPlanning.xls

It is a simple file to keep track of incoming delivery orders.
Column A: the date the order comes in.
Column B: clients (fictional).
Column C: number of pieces to be delivered.
Column D: the date the pieces arive.
Column E: the date the pieces has to be delivered.

The rest of the columns contain calculations to prioritize the orders.
The data in columns A, B, C, D and E needs to be entered and I thought it would be nice to create a userform to do so. I created a userform called frmNOI. This userform is accessed by clicking the button on cell M4.
Since I have 3 fields with dates I thought it would be handy if I create a calender to easily pick a date. The calender userform is called frmCalender.
I think I have all the necessary code implemented, BUT how do I make the calender appear when a field, where a date needs to be entered, is selected on the userform frmNOI (from top to bottom field 1, 4 and 5).

Kind regards,
Trowa

PS. I found a great site for translating functions which might interest you. Among others it has english, dutch and french function names. This is the site: http://dolf.trieschnigg.nl/excel/excel.html
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hello Trowa,

Happy to see you again too ;) It was a long time.
The new file is here : https://authentification.site/files/19353396/PostPlanning_2.xls
Hope this is what you expect. You have to double-click in the textbox to display the calendar and after to double-click on the date to select it.

Have a good day and see you soon.

PS : Concerning the site link for translating excel functions you give me, I already had it in my bookmarks but thank you for having thought.

Hi Aquarelle,

Great work, you made it work for two date fields which gave me the opportunity to figure out what you did and practice what I have learned by making it work for three date fields.

Here is my revised file: https://authentification.site/files/19371253/PostPlanning_3.xls

There is however one thing I don't understand and that is the format of the dates. The format I want is "dd-mmm-yy". Since it is different then I first wanted, I changed the lines of code in the userform.
When I open the userform the dates are in the right format and when I click OK the dates are placed in the right cells in the right format.
When I however double-click a date field to make the calender appear and pick a date the format on the userform is "d-mm-yyyy". When I click OK the day and month of the date is switched around (!) when placed in the cell (I double checked the format of the destination cell, which is correct).

How is this possible and more importantly how do I keep the preffered format of "dd-mmm-yy"?

Lastly I want to ask you some minor adjustments:
- When the userform is opened, today's date is shown. I would like the fields to be blank, so you can see which fields you still have to fill.
- When one of the calenders is opened I still would like to start on today's date, unless there is a different date filled in. This because when a mistake is made, I don't have to select the month again but can easily pick the date next to the one I mistakenly picked.

You are truly a great help.
Kind regards,
Trowa
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
489
Hi Trowa,

Just few words to tell you that today I haven't time to look at the macro but I'll try this week-end.
Concerning the date format "dd-mmm-yy", you have to format all the date column cells with this format by using "Format"--->"Cell" ...

Best regards and see you soon