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.

[vba]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[/vba]

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 (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:

[vba]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 [/vba]
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:
[VBA]For i = -20 To 50[/VBA]

to this:
[VBA]For i = -90 To 50[/VBA]

[xluser2007]

Change this line in the initialize procedure:
[vba]For i = -20 To 50[/vba]
to this:
[vba]For i = -90 To 50[/vba]

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.

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

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

[vba]ActiveCell.Offset(1, 0).Select [/vba]
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.