Solved: Date Picker Control

[xluser2007]

Hi All,

I am not experienced with UserForms, but am in the process of designing a worksheet with some userform elements.

One such Userform element i want to add into my worksheet is in cell b4, I would like the user to input their age. To introduce a validation element (and make it a bit fancy), I would like them to enter it via a calendar Date Picker Userform that pops up when the user activates B4.

Could anyone please guide me on how to do this, or of there are any available date pickers avalalble to use and how to go about creating the above.

I thought I saw one provided at VBAX a while back as an example, but can't seem to find it.

It would be great for me to learn this.

Thanks and regards,

[GTO]

Greetings xluser2007,

Presuming you have inserted a calendar control onto the sheet, rename the control to: "Cal_DOB" or: change code to suit.

Option Explicit
'// This enters the value of the calendar into B4 and hides//
'// the calndar.//
Private Sub Cal_DOB_Click()
Range("B4").Value = Cal_DOB.Value
Cal_DOB.Visible = False
End Sub

'// This shows or hides the calendar if B4 or another cell are //
'// selected.//
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
Cal_DOB.Visible = True
Else
Cal_DOB.Visible = False
End If

End Sub

I would note that this doesn't check to see if multiple cells are selected or anything. Just a simple snippet to see if this is what you had in mind.

Hope this helps,

Mark

[xluser2007]

Hi Mark,

Many thanks for your prompt reply.

My apologies if I had not specified clearly in my original post, but I have never really created a UserForm, and as such don't have one prepared for a date picker.

I was searching through VBAX and thought I'd seen one done as an example, or asking if anyone has a Calendar date picker UserForm available for me to use.

As such, would you know of any Userforms that you have seen online for this purpose and how to modify your above code to suit?

again, thanks for your interest.

[xluser2007]

Hi Mark,

A new development :).

I found this great site: http://www.fontstuff.com/vba/vbatut07.htm

This has a Calendar Picker in built, if you download the workbook (http://www.fontstuff.com/downloads/Excel%20Calendar%20Workbook.zip) (not addin).

Mark, using your helpful event driven code above, could you please explain how to modify the workbook so that the Calendar picker only pops up when you select B4 on Sheet1 and no other cell.

Any help is sincerely appreciated.

regards,

[xluser2007]

Hi Mark,

I was just experimenting and inserted your code as below into Sheet1:

Option Explicit
'// This enters the value of the calendar into B4 and hides//
'// the calndar.//
Private Sub frmCalendar_Click()
Range("B4").Value = frmCalendar.Value
frmCalendar.Visible = False
End Sub

'// This shows or hides the calendar if B4 or another cell are //
'// selected.//
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B4")) Is Nothing Then
frmCalendar.Visible = True
Else
frmCalendar.Visible = False
End If

End Sub
It comes up with the error as shown in the attached screenshot.

Could you please advise on how to correct this error?

[GTO]

You have my apologies. I read your question too fast, complicated further by seeing your post numbers, and somehow, my drain bamage got me thinking that you wanted a userform type (activex) of control to display.

Anyways - it is too late, and I am on my malingering, slower than a sloth laptop at home. Most likely you will have already received better direction than mine by tomorrow, but I'll try and check.

In the meantime, I would mention this. If you're just trying to 'fancy up' the sheet a bit, rather than add a userform, how about we just add the control. This will take you but a few minutes (tops) and you'll start to learn the calendar control.

If you want to try this:

Display the visual basic toolbar, then display the toolbox. This gives you the ActiveX controls. Now hover over the buttons 'til you find "Additional Controls" (or similar) and click on this. Scroll down 'til you find the Calendar Control, and click it. Then drag on the sheet as big as you want it.

BEFORE you deselect the newly created control, type the name I suggested (stop: you need to ensure the formula bar is already displayed before all this; sorry) into the box at the left end of the formula.

Now try the code :-)

Mark

[lucas]

Calendar control will cause problems if you send it to someone who doesn't have the calendar control installed. See this thread for a discussion:
http://www.vbaexpress.com/forum/showthread.php?t=10914

Alternative is a userform calendar which opens on worksheet selection change. If a cell is formatted a certain way the calendar opens. See attachment.

[xluser2007]

You have my apologies. I read your question too fast, complicated further by seeing your post numbers, and somehow, my drain bamage got me thinking that you wanted a userform type (activex) of control to display.

Anyways - it is too late, and I am on my malingering, slower than a sloth laptop at home. Most likely you will have already received better direction than mine by tomorrow, but I'll try and check.

In the meantime, I would mention this. If you're just trying to 'fancy up' the sheet a bit, rather than add a userform, how about we just add the control. This will take you but a few minutes (tops) and you'll start to learn the calendar control.

If you want to try this:

Display the visual basic toolbar, then display the toolbox. This gives you the ActiveX controls. Now hover over the buttons 'til you find "Additional Controls" (or similar) and click on this. Scroll down 'til you find the Calendar Control, and click it. Then drag on the sheet as big as you want it.

BEFORE you deselect the newly created control, type the name I suggested (stop: you need to ensure the formula bar is already displayed before all this; sorry) into the box at the left end of the formula.

Now try the code :-)

Mark
Hi Mark,

There is no need to apologise. Thanks for replying, atleast now I know which method you were suggesting. And it is always good to have an alternative method in place, from the Userform Calendar method I was considering for this particular project.

Steve's (lucas') method is more precisely what I was after for this task (a Userform approach). The Excel users that this simple form will be sent to are not necessarily that savvy, in that they may not have the Calendar Control installed, and will be difficult to explain the technicalities of installing it to them.

For my knowledge, I would like to try out your method (Using the built in Calendar Control) as well. I will keep you posted with any queries that I have.

Steve, many thanks for sharing your wonderful method.

Just one thing, how do I get the Calendar picker to select dates starting from the earliest possible Excel Year (1900). Some of the USer's may be born around 1930's, so I want to cater for them.

It is cool to see Excel being utilised like this!

thanks and regards.

[lucas]

Just one thing, how do I get the Calendar picker to select dates starting from the earliest possible Excel Year (1900). Some of the USer's may be born around 1930's, so I want to cater for them.


Change this line in the initialize procedure:
For i = -20 To 50

to this:
For i = -90 To 50

[xluser2007]

Change this line in the initialize procedure:
For i = -20 To 50
to this:
For i = -90 To 50
Thanks Steve, that works really nicely.

I have one other question, if the user activates the cells with the target format, the form shows up. Now if the user enters a date in the target via Userform it displays it correctly in that cell, as required. The target cell is then selected once the form enters the Userform selected date into it.

Now suppose the the user made an error in selecting the date via form (quite likely), the only way to re-enter the date in the target cell is by selecting another cell and then re-selecting the target cell, so that the Userform pops up again.

Q: Is it possible to create a set of multiple events in the worksheet where by the form gets activated if you: select or single-click or double-click the target cells?

Basically, I am trying to ensure a form of validation whereby the User can't manually enter the date on the target cell without using the Userform, and whereby it is very easy to access the UserForm to re-enter the date.

The User's may not understand that they have to select another cell and re-activate the target cells and they may start to manually re-enter the correct date, defeating the whole purpose of the calendar Userform.

If you could help in clarifying how to do the above, I would be really grateful.

thanks,

[Aussiebear]

Is there a set or preferred order in which the User selects cells?

[xluser2007]

Is there a set or preferred order in which the User selects cells?

Hi Aussiebear,

It is not quite the case of the order of selecting the cells.

At this stage it is just one cell, B4 on the worksheet.

Steve's cpode works really well. However it works when the user selects B4 in this case. Once the user selects B4, the calendar pops up and the user enters the date, the Userform puts the date in B4 and B4 is the active/ selected cell.

Now if the user made a mistake selecting the date and wanted to change it, they would need to select/activate another cell e.g. B5 and then select/ activate B4 to enter the date via the calendar userform.

The problem with this is that the user needs to understand that thye need to select another cell and then re-selct B4 to enter the correct date via the calendar.

I don't want them to manually input it by typing it and defeat the purpose of the cool userform.

This is why I was suggesting that there be different ways of accessing the calendar on B4, by selecting, single-clicking, or double-clicking it.

Hope this makes my requirement clearer.

Alternative thought: Instead of trying to build the above three events for the calendar, I was wondering how to get the calendar enter the value in the target cell and finsih by selecting the cell just beow it. This way, igf there is a mistake the user is forced to re-select B4 and the calendar pops up as required. Any ideas on how to do this?

[GTO]

Hello xluser,

Thank you for being gracious at #8, and after seeing Steve's and others comments about potential problems with the app's calendar control to boot... Well, I didn't mean to "bail", but did want to read BlueCactus' KB article. I'm still studying the calendar, but saw your question, and thought I might be able to do better by ya this time.

While there are a number of events, selection change, double-click, and right-click amongst them, these wouldn't really force the user to activate the event (excepting of course your already used selection change), so I'd think your next idea of just jumping down a cell a good one. Now since the user can probably dismiss the userform by either an <OK> (or similar) button, as well as some type of <Cancel> button, as well as the little close button (the "X") at the upper-right hand corner of the userform, I would try just using the QueryClose event under the UserFom's module. This way, no matter how the form is dismissed, the next cell down is selected.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
ActiveCell.Offset(1, 0).Select
End Sub

Hope this helps,

Mark

[lucas]

Mark has a good solution. The alternative would be to add the offset code to each buttonclick event which would be tedious.

I use this method for checkboxes created with marlett fonts and it works just fine.

[GTO]

Thank you much Steve; not only for the comment, but catching and suggesting the UF calendar.

Respectfully, even adding code to ea bttn, wouldn't this leave the user able to defeat by by use of the "x" close bttn at the top of the form? (I am of course assuming the "x" bttn and/or the titlebar hasn't been hidden thru API.)

Hope yer havin' a nice Sunday,

Mark

[Bob Phillips]

Here is an adaptation of Steve's calendar that supports select and double-click

If you check the code, you will see that it will be a simple exercise to remove the select pop-up.

[lucas]

Hi Bob, I had wondered if a class file wouldn't be a better way to handle all of those buttons......nice.

Thanks for that.

[xluser2007]

Hi Mark, Bob, Steve,

Thank you for your your great contributions, it's thanks to experts sparing their time and knowledge that noobs like me learn anything.

Mark, your solution is interesting using the QueryClose event to offset the activecell.

However based on your comment regarding any issues faced with this method in Post#13, Bob's method is probably more suited to this purpose. Thank you for your input though, I'm sure I can use this in another application.

Bob, thank you for your great manipluation to the existing method to add the double click methodology.

Just one question though, how can I force the user to not be allowed to enter any data (or should I say date-a) manually. If they start typing, it should prompt them to double click, or even better pop up the Userform. So in this case the Userform is not just popping-up on selecting or double clicking, but also of they try to manually re-type a value in the cell.

BTW, what you have done is fantastic, I was just wondering if this is possible as it is a form of validation and ensures that the user is forced to use the Calendar for the purpose that it was built for in the first place.

thanks and regards,

[xluser2007]

Hi Mark, Bob, Steve,

A follow up from my previous post.

Mark, I tried your's and Steve's suggestion of adding the

ActiveCell.Offset(1, 0).Select
to each of the 42 button click events.

This works nicely (but as Steve said there is the one-off tedious part of pasting it 42 times). The more tedious part may be if we needed to make changes to the action e.g changing it to ActiveCell.Offset(5, 0).Select for each of the buttons for example. Q: As such, implementing this change may be easier to do using Classes as per Bob's code, but am unsure how to go about this, any ideas?

Also the only problem is if you hit the "X" mark, as you identified in Post#15 Mark, on the UserForm, the method fails and the user can manually input the data. Q: Is there any way offset by one cell below if the Userform is closed (I think this is what you were suggesting, but I didn't quite understand how to go about doing it)?

Bob, with your solution, I re-tested it, and couldn't get the double-click to work on target cells. Q: Could you please explain where I may be going wrong?

thanks all,

[Bob Phillips]

Sorry mate, I did a bad job of the implementation. The class code was okay, but I messed up with the double-click. This version shoud be better.

Catching input as thye type is a lot harder. Once a user goes into edit-mode our code doesn't get a look-in. We could react to the change after it is done, but they could easily exit this.

A better way maybe to lock the date cells and protect the sheet. You can then elect and double-click the cells, and you will have to unlock - add date - lock, but they won't be able to add anything else. And I would suppress the X on the calendar.

[Bob Phillips]

Sorry, here is the file

[xluser2007]

Sorry mate, I did a bad job of the implementation. The class code was okay, but I messed up with the double-click. This version shoud be better.
Bob, thanks sincerely for your efforts on this. No need to apologise. The calendar pop-up with double-click is awesome.


Catching input as the type is a lot harder. Once a user goes into edit-mode our code doesn't get a look-in. We could react to the change after it is done, but they could easily exit this.
Agree with you that this route is difficult, but I reckon if we can just get your class module to offset the target cell by one every time the user enters the date via the calendar we can avoid this problem without having any issues with checking for manual user input.



A better way maybe to lock the date cells and protect the sheet. You can then elect and double-click the cells, and you will have to unlock - add date - lock, but they won't be able to add anything else. And I would suppress the X on the calendar.
This is a really good idea. I reckon though coupling the disabling of the "X" on the Userform along with the previous point of moving off the target cell after entry would allow the objective of non manual user-entry relatively easy to achieve.

To hammer the point of non-manual entry, it is probably easiest to make the Userform modal, so that the user doesn't (accidentally manage to get out of the form without entering something).

What do you think about these suggestions? And if you agree, could you please show me how to implement these.

thanks and regards,

[Bob Phillips]

Doesn't really matter if I agree or not, you know your users and you have obviously have thought it through.

Does this do what you want?

[xluser2007]

Doesn't really matter if I agree or not, you know your users and you have obviously have thought it through.

Does this do what you want?

Bob, this is wonderful, does exactly what I want :clap:.

I am marking this thread solved.

Having solved the core problem, and I know I am getting carried away, but I have an additional query. At the moment, the Userform pops up as default with the year 1930 (the FIRST_YEAR variable).

This is cool, but for a couple of cells B9 and B10, I would like it to display on this year and the previous year. This is a form of validation to ensure that only dates in these years are entered and also to make it convenient for the users to enter it via the Userform.

I know that it could be done by applying simple data validation for these cells, but I don't want to confuse these users too much and have the calendar gently guide them, along with written prompts.

Any ideas on how to do this, in effect it will be calling the Userform but passing through FIRST_YEAR and LAST_YEAR as paramaters when calling on the Userform. Any ideas if this is easily possible?

[Bob Phillips]

Here is one way

[xluser2007]

Here is one way

nifty stuff, gotta love Classes ehh.

A couple of queries:

Q1: how did you get the orange cells to start at 2008 if the range from 1930-2008 (just wondering how it figures out the start year for the drop down).

Q2: Also, I forgot to ask which line of code disabled the "X" mark for the Userform?

Thanks again.

[Bob Phillips]

nifty stuff, gotta love Classes ehh.

I certainly do. I rarely if ever have a substantial project nowadays that doesn't have classes.


Q1: how did you get the orange cells to start at 2008 if the range from 1930-2008 (just wondering how it figures out the start year for the drop down).

It checks in the FormLoad procedure if the current year is within first year and last year, if so, it sets the ListIndex property to wher this year is within the array of years. If it isn't, it sets ListIndex to 0, the first in the series.


Q2: Also, I forgot to ask which line of code disabled the "X" mark for the Userform?

There is some Userform_QueryClose event code where I set the Cancel property to True if the CloseMode is 0 (the X).

[Bob Phillips]

BTW, you can call APIs to remove the X from the caption bar of the form, I just don't see the point personally.

[xluser2007]

I certainly do. I rarely if ever have a substantial project nowadays that doesn't have classes.



It checks in the FormLoad procedure if the current year is within first year and last year, if so, it sets the ListIndex property to wher this year is within the array of years. If it isn't, it sets ListIndex to 0, the first in the series.



There is some Userform_QueryClose event code where I set the Cancel property to True if the CloseMode is 0 (the X).
Thank you for the explanations Bob, always amazed by your grasp of this stuff.



BTW, you can call APIs to remove the X from the caption bar of the form, I just don't see the point personally.
Yeah, in the spirit of getting carried away, I went and started searching for this functionality. I found Stephen Bullen's FormFun (http://www.oaltd.co.uk/DLCount/DLCount.asp?file=FormFun.zip) example, which is pretty cool. he also needs the Userform_QueryClose event code as you have implemented to prevent the the Alt+F4 shortcut to close the form, so I think the method you have proposed is best.

I might just add in a MsgBox to explain that "X" has been disabled on the UserForm to your existing code.

Many thanks for your help and insights bob. And also Mark and steve for your earlier posts.

Nice to learn something new.

[xluser2007]

Hi Bob,

In post #25, you kindly showed me how to allow the calendar to only show specific years for specific cells by passing those cells and the constraints to the Userform.

I have just one more query on this. Is it possible for certain cells, which already have a calendar pop up to be coded to display 30 June values for those specified years.

I have attached my Sample workbook. In the named range cell "Section2_b" and "Section6_b", I would like the user to select a Date using the calendar, but only a 30 June date. If they Select a non-30 June date then there should be quick vbOKonly Msgbox (which is modal), telling them to input it correctly, and the modal calendar Userform should appear again till they select a 30 June date.

Any help with this is appreciated.

regards,

[xluser2007]

Sample workbook attached as per post#30.

[Bob Phillips]

Why bother with the calendar if they can only have one date, why not just load 30th June when they select either of those cells?

[xluser2007]

Why bother with the calendar if they can only have one date, why not just load 30th June when they select either of those cells?

Good point Bob,

But it could be 30 June of any previous year up to say 10 years.

The alternative was to allow them to pick a year in a new cell and have the 30 june value come through.

I was just wanting to use the single availible input cell, and use the prebuilt modal calendar to validate the input with a MsgBox. This is because for all other user-filled date inputs the User requires the calendar, so I figured best to go ahead with it.

The otehr question i had was, for these cells, is it possible to have the calendar display only 30 June dates by changing some code to remove all months and days except 30 june, just curious?

As per your suggestion, a good alternative could be to create a validation list of 30 june dates and just create a drop-down to pick from.

I was more curious than anything - the calendar form is pretty nifty.

Thanks for your insights as always Bob.

[Bob Phillips]

Good point Bob,

But it could be 30 June of any previous year up to say 10 years.

Okay, didn't fully appreciate that.


The alternative was to allow them to pick a year in a new cell and have the 30 june value come through.

I was just wanting to use the single availible input cell, and use the prebuilt modal calendar to validate the input with a MsgBox. This is because for all other user-filled date inputs the User requires the calendar, so I figured best to go ahead with it.

It is a question of appropriateness IMO. Giving them a full-blown calendar and then throwing an error if they pick any 364 of 365 possible dates seems a tad perverse to me.


The otehr question i had was, for these cells, is it possible to have the calendar display only 30 June dates by changing some code to remove all months and days except 30 june, just curious?

As it is a custom calendar not a calendar control, that should be possible ... but wouldn't b e how I would do it. You would just pass some value to the form for this instance, say a Boolean variable June30Only, and build the calendar with


Private Sub Build_Calendar()
Dim mpDate As Date

'the routine that actually builds the calendar each time
If CreateCal = True Then

CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
'sets the focus for the todays date button
CommandButton1.SetFocus
For i = 1 To 42

mpDate = DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value)))

If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then

Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then

Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
End If

If Format(mpDate, "mmmm") = ((CB_Mth.Value)) Then

If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
Controls("D" & (i)).Font.Bold = True

If Format(mpDate, "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
Else

If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
Controls("D" & (i)).Font.Bold = False
End If

Controls("D" & (i)).Enabled = Not June30Only Or (Day(mpDate) = 30 And Month(mpDate) = 6)
Next

End If
End Sub


and add this code in the FormLoad procedure before Call Build_Calendar



If June30Only Then

CB_Mth.ListIndex = 5
End If



As per your suggestion, a good alternative could be to create a validation list of 30 june dates and just create a drop-down to pick from.

I was more curious than anything - the calendar form is pretty nifty.

That is the way I would do it. Far better to have an appropriate diaogue IMO that just using something becuase you use it elsewhere.

[xluser2007]

Okay, didn't fully appreciate that.



It is a question of appropriateness IMO. Giving them a full-blown calendar and then throwing an error if they pick any 364 of 365 possible dates seems a tad perverse to me.

[quote]

Absolutely agree Bob, simple listed data validation is the best way to go.

[quote=xld]

As it is a custom calendar not a calendar control, that should be possible ... but wouldn't b e how I would do it. You would just pass some value to the form for this instance, say a Boolean variable June30Only, and build the calendar with


Private Sub Build_Calendar()
Dim mpDate As Date

'the routine that actually builds the calendar each time
If CreateCal = True Then

CalendarFrm.Caption = " " & CB_Mth.Value & " " & CB_Yr.Value
'sets the focus for the todays date button
CommandButton1.SetFocus
For i = 1 To 42

mpDate = DateAdd("d", (i - Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value))), _
((CB_Mth.Value) & "/1/" & (CB_Yr.Value)))

If i < Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then

Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
ElseIf i >= Weekday((CB_Mth.Value) & "/1/" & (CB_Yr.Value)) Then

Controls("D" & (i)).Caption = Format(mpDate, "d")
Controls("D" & (i)).ControlTipText = Format(mpDate, "m/d/yy")
End If

If Format(mpDate, "mmmm") = ((CB_Mth.Value)) Then

If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H80000018 '&H80000010
Controls("D" & (i)).Font.Bold = True

If Format(mpDate, "m/d/yy") = Format(ThisDay, "m/d/yy") Then Controls("D" & (i)).SetFocus
Else

If Controls("D" & (i)).BackColor <> &H80000016 Then Controls("D" & (i)).BackColor = &H8000000F
Controls("D" & (i)).Font.Bold = False
End If

Controls("D" & (i)).Enabled = Not June30Only Or (Day(mpDate) = 30 And Month(mpDate) = 6)
Next

End If
End Sub

and add this code in the FormLoad procedure before Call Build_Calendar



If June30Only Then

CB_Mth.ListIndex = 5
End If



This is excellent Bob. I am amazed that every time I put up a bit of a challenge you quickly come up with a nifty solution. Well done!


That is the way I would do it. Far better to have an appropriate dialogue IMO that just using something becuase you use it elsewhere.
Yeah the Calendar has many good uses, but I understand it shouldn't be overused just because it merely looks good to the user (or designer ;)).

kind regards