Userform to select dates but can't limit them between each other

[Tolga]

Hello all,

I prepared userform to create requests.

Request Nr. and Request Create Date comes automatically.

I have to select Controlled, Modified and Released dates from calendar (Date Picker).

I have some bugs in my file

1) Calendar always opens the date that i created the file.
2) I set limits between the dates but it doesn't work properly. I think it is because of date format.

Ex.
Created Date:08/07/2015
Controlled date: Can be 08/07/2015 or future

but i can select 09/06/2015 from the pop up calendar, the code only checks if the day is smaller or bigger than the created date, i want from it to compare day month and year together

3) I don't want users to enter dates manually and copy paste into textbox areas. I can prevent manual entering always but sometimes user can copy paste sometimes can not.

 

[Luke M]

Instead of trying to build your own calendar, and getting some confusion in XL between interpreting your date as dd/mm/yyyy or mm/dd/yyyy, you can use the already existing date picker. I put them in, changed displayed format to be unamibguous, and set initialization macro to preload today's date (and set Min Date value). The date picker lets you prevent manual freeform entry and setting the boundary conditions.

 

[Tolga]

@Luke M thanks for your response.

It is a good advice to use standard date picker instead of user created calendar. I agree with it.

But in this situation i can't limit the dates between each other. I don't want all of their min value set to DATE.

Ex.
Created 12.07.2015 ,
Controlled 15.07.2015 (can't be before Created)
Modified 13.07.2015 (can be before Controlled but not before Created)
Released 16.07.2015 (can't be before Created,Controlled,Modified)

In my own calendar i can limit them when user clicked and selected the date:

If Me.txtControlled.Value < Me.txtCreated.Value Then
MsgBox "Controlled date can't be before created date!"
'clear the data
txtControlled.Value = ""
ElseIf Me.txtControlled.Value >= Me.txtCreated.Value Then
End If

 

[Luke M]

Based on the above, at the worst case, none of the dates can be before created, so none of the dates can be before Date. Correct?

For modified, does it have to be before controlled, or just that it can be?

For Released, easy enough to put in some change events to modify the minDate. In this one, put in the 2 change events.

 

[Tolga]

None of the dates can be before Created *

Controlled must be >= Created
Modified must be >= Created ( No relation with Controlled)
Released must be >=Created and also Released must be >= Max Date of Controlled,Modified.

I tried many things but could't break the relation with Controlled and Modified.

And the other issue is, i don't want the dates to be set automatically ex. i select Controlled as 20.07.2015 from DTPicker then Modified value set to 15.07.2015 and Released date set to 20.07.2015.

If select Controlled, the others must be empty. The user has to be able to select or not them acc. to the code.

 

[Luke M]

From the form design, it looked like user needs to fill in all the dates. Is this not the case?

I tried many things but could't break the relation with Controlled and Modified.

I'm not sure what you mean by this statement...it doesn't look like there is any relationship betwee these two.

You statement about not setting dates automatically is slightly in conflict with the min value...if the date can't be before created, then it can't be null, so in essence, teh date has "been picked". But, if it is causing confusion, we can just tweak the formatting so it doesn't look like anything is there until user picks a date.

 

[Tolga]

There was a relation in requests LM2.xlsm because i modified your code and forget to undo so i thought you made it like that.

This is what i need requests LM3.xlsm with some improvement. It doesn't allow Controlled to select the same date with Created at first selection. If i want to select today for Controlled i have to select another day then relesct today.

Also for released, if want to select max date of (controlled,modified) i have to select another day bigger than max and then i am available to select max date.

i think it is because of =minval formula.

 

[Luke M]

It's because I was using the Dropdown_change event, but in those cases, we're not changing it...I'll switch to using the Dropdown event, so that if user opens up the calendar, the macro fires.

 

[Tolga]

thanks for the new update.

with _change , date value appears after user clicks and selects the date but the user can't select min date at first, he has to select another day and then he is able to select min. date

with_dropdown , date value (min.date) appears after user clicks dropdown arrow. if it can be null until user selects the date it will be more useful.

maybe we can mix them

 

[Luke M]

I'm very confused as to how you want the form to work.
If the user clicks on dropdown, they are entering a date...but you don't want a date, unless they pick a date...which they've just indicated they want to do by opening the calendar.

Your setup gave the impression that all fields will be filled in. Is that not so, ie you can have a Modified Date but not a Controlled Date?

 

[Tolga]

*All the fields must be filled in.

With current setup "requests LM4.xlsm" when user clicks arrow down on calendar, the date automatically written to the form according to .min date values.

Although the users exits without selecting a date, the date automatically chosen because the down arrow clicked.

I meant this. I think it is not possible.

 

[Luke M]

Sounds like the current setup is more of a feature than a hindrance then, as it automatically helps the user. Since all the fields are mandatory, might as well have the computer help the user fill in the form as much as possible.