Solved

How to add date picker in an Excel 2010 Table

Posted on 2015-02-18
21
244 Views
Last Modified: 2016-02-11
Using:  Excel 2010

I'm running into problems trying to get a date picker to work in an Excel 2010 Table.

I've gone as far as inserting the date picker object but am having difficulty applying the date picker object to the correct cell in my table.

I inserted the object via:

Developer > Insert > More Controls > Microsoft Date and Time Picker Control 6.0 (SP6)

I will need to use the date picker next to the active cell (imagine a behaviour similar to a drop down menu on a web page where the cell you pick remains visible and once selected, the Picker window opens up just below and to the right of the active cell) and the active cell could be any initially blank cell in a column, or could be a stand-alone cell, and could be on different worksheets within the workbook.

For clarity, I want the date which is entered to go only in the one active cell, not into several of them at once spread about the workbook.

I've tried various ways and always run into one snag or another.

So ... I can insert the Date Picker object but I don't understand how to format it correctly (size it), where to place it, and how to apply its pop-up behaviour to a target cell.  The other issue I have is that I don't know how to turn off the display of the Date Picker object I inserted; I only want it to appear after a particular cell (which I designate as a target cell) is selected.

Thx
0
Comment
Question by:qeng
  • 11
  • 10
21 Comments
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40618240
Consider this a preliminary version. It's based on my Magical Floating ActiveX Control article. Click any cell in column A.
Q-28619384.xlsm
0
 

Author Comment

by:qeng
ID: 40620764
Martin,

Thanks, very slick.  The only part that seems to be missing (sorry I'm a VBA newbie) is to cause the cell to show the date after it's been selected in the popup and then close the control box.

At present, after I select the date in the popup, the day remains highlighted in the control but nothing gets entered into the cell (whether I hit enter or click back into the same cell I activated the control from).  The date only gets entered after I click somewhere else (whether in column A or elsewhere on the worksheet).

If it was just me using it, the above behaviour wouldn't be an issue since I know what it's doing, but with multiple users who will use the workbook i'm working on, it will throw too many of them for a loop.

How can this be tweaked to automatically enter the date into the cell after it's picked in the popup and then close the control box, until the next cell is clicked?

I love the fact that the previously chosen date appears as a default for the next cell since that will come in very handing for some of my users who will need to enter the same date several times in a row (this is quicker and more intuitive than cutting and pasting the date over, especially if it's only in the next one or two rows that the date needs to be repeated).

Thx
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40621396
Add this code to the sheet.

Private Sub DTPFloat_CloseUp()
DTPFloat.Visible = False
ActiveCell.Offset(0, 1).Select
End Sub

Open in new window

0
 

Author Comment

by:qeng
ID: 40622367
Hah ... so cool Martin ... it works, the control box now closes after the date is entered ... but :)

One last little bit ... it loves putting in dates so much, I can't keep it from leaving at least one date in the worksheet :)

What do we need to add to the code so that if the user deletes a date in Column A and wants to move elsewhere in the worksheet or workbook (say to work in an area where we're entering text or numbers and we don't want the date pop up to load) the code doesn't add just one more date in Column A?

In other words, I can't get rid of the last date :)
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40622396
This assumes that you either right-click->delete the cell or the row. I haven't been able to figure out how to remove the date via selecting the cell's contents and then delete or by deleting one character at a time.
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(ActiveCell, Columns(1)) Is Nothing Then
    DTPFloat.Visible = False
End If

Open in new window

0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40622433
One version of my Magical Floating ActiveX Control code triggers the display of the ActiveX control (in your case the date time picker) only when the cell is double clicked. So if you are willing to have the date time picker pop only when the user double clicks the cell then that would make deletion easier. Let me know if you want to do that and I'll make the required changes.
0
 

Author Comment

by:qeng
ID: 40622897
Martin,

Unfortunately, as you suspect, right-clicking the cell won't be intuitive (and it then asks whether to shift the cells up, down, right, etc.) and for the same reason deleting the row won't work in my application because I need to use a date picker in a large formatted table where date is just one of the fields.

So for example, imagine this is being used by a large magazine publisher and the managers use a workbook to keep track of all of the articles they plan to publish, say, one worksheet per month.  Imagine that on each worksheet, there is a long list of articles, which contains columns for ArticleID, Author, ArticleStatus, and say ReviewMeetingDate.

If someone tries to enter a date for a in the ReviewMeetingDate field and then decides they don't want that date because they have to cancel the meeting, one doesn't want to delete the entire row of data to get rid of the date, nor to shift any cells around since the entire table is rigidly structured (like a database table).

I think the double-click is a decent workaround if you can help me with that.  It's a little less intuitive than single-click but I could add instructions, a note or if I can figure out how to add a tooltip, I could convey the usage-instruction easily to the user.
0
 
LVL 45

Assisted Solution

by:Martin Liss
Martin Liss earned 500 total points
ID: 40623454
OK, here's a double-click version (with a tooltip).
Q-28619384b.xlsm
0
 

Author Comment

by:qeng
ID: 40623629
Very slick.  Thanks for staying with this Martin.

It's now erasable!

How can I embed, in the code, the named ranges (there would be different non-adjacent ranges, and some single cells) where I would like to use this magical Date Picker?

Right now it's targeting ColumnA and starting in row1 but I will want to use it in different named ranges (some will be in Tables, others will be single cells used for date entry).
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 40624146
In this version I created a named range called "NamedRange1" which encompasses Column A and  a second one called "NameRange2" for cells C5 to C13 and modified the code so that it only affects those two ranges plus cell E2. Note that only column A has the "tooltip" that works via Data Validation and you should probably add similar Data validation to any area that you want to double-click.
Q-28619384c.xlsm
0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

by:qeng
ID: 40624492
Hey Martin, it works, it works!!

Just as a heads up, there were a few instances where I would select a date from the pop-up calendar and nothing would get inserted in the cell.  It happened in different locations (in appropriate NamedRanges) but eventually sorted itself out.  Not sure why the cell targeting or data xfr didn't happen a few times.

Two last tweaks if that's possible without opening up a can of worms.

Tweak1 - Change displayed date format upon double-clicking from dd/mm/yyyy to yyyy/mm/dd

Although the pop up calendar makes it clear after it opens, which month we're dealing with, a date in the format dd/mm/yyyy can easily be misunderstood to be mm/dd/yyyy (especially if some users have date data set as 'Mar 07, 2015' elsewhere in the workbook).  I know I can format the date cells to get around this issue but I'm trying to guard against someone seeing:

06/05/2015 and thinking that that's June 05, 2015 (instead of the May 06, 2015 it's intended to be) and hitting enter without opening up the pop-up calendar (see Tweak2).  

Even without Tweak 2 implemented, people's brains go on autopilot when they start doing a lot of repetitive tasks and if the user sees a date thinking it's in June when it fact it's in some other month <13, they could well open up the calendar, click on the desired dd value, and move on, not realizing they're not in the right month (even if it's spelled out at the top of the pop-up calendar).

Is there a simple way to tweak the code so the control box with the date value which first opens up after the cell is double-clicked (before the drop down arrow is selected to open the pop-up calendar) displays the date in the format:  yyyy/mm/dd?

Tweak2 - Is it possible to have the user simply hit Enter to accept the date shown in the control box (when it first opens after double-clicking the cell) without having to go to the pop-up calendar to re-select the same date

I know users would be able to do a cut and paste on a previously entered date to fill in their required dates but it would be quicker if they could just double-click/enter; double-click/enter ... if the last entered date is still applicable.

In the application I'm working on, there will be numerous documents being subjected to review meetings and it will happen often that the person scheduling the meeting will want to re-use the same date for several cells, not always contiguous.  

So the ability to re-use the last entered date like this will be a handy feature if that's possible.

Thanks again.  This will be so slick if it works (it's already 90% there!).
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40624612
You're really getting your money's worth here. I'll give it a try later today.
0
 

Author Comment

by:qeng
ID: 40624880
Thanks Martin.  This wasn't to take advantage, it's just that it's so close.  I can close this question off and repost if you prefer and bite size it but figured we'd made such good advances ...
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40624882
No problem.
0
 

Author Comment

by:qeng
ID: 40624883
Honestly appreciate it.  I'm learning a bunch as we go here too.  I've been following along in the background.

Thx
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40624886
I've got the first part done. While I'm at it, it's possible to set a valid date range. Is that something you'd like?
0
 

Author Comment

by:qeng
ID: 40624895
Actually that is a very nice bonus to have.  I would have felt sheepish asking but I know I'll be using this sub in many projects and I can see that getting some good use.

Don't sweat it if the valid date range becomes an issue but definitely a nice 'pro' feature to have.

Thx
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40624899
Well let me ask you this. Is it a problem if the user were to enter a date from next week?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40624931
The date in the dtp is now displayed in yyyy/MM/dd format.

Pressing Enter to autofill the cell doesn't really work because you would need to manually select the dtp first and then press enter. Even if you wanted to do that it's problematic because the date in the dtp can easily be moved left, hiding part of the date. Instead of that I added a macro called FillNext and gave it a shortcut key of Ctrl+Shift+D. When invoked it copies the text of the active cell to the cell below it.
Q-28619384c.xlsm
0
 

Author Comment

by:qeng
ID: 40625053
Thank you so much Martin.  

I made a small tweak to the last version (we had a previous ----c.xlsm versions, for the record, so I renamed it ----d.xlsm).  As received, it was selecting dates everywhere in the worksheet due to a REF error on NamedRange2 in the workbook.

By redefining NamedRange2, I was able to have the picker working in the specified ranges.

The 'press Enter to autofill the cell with displayed date' after double-clicking a cell is no big deal.  Convenient but not an issue since users can easily copy the date from elsewhere or re-enter.  

The main piece was the formatting of the date displayed in the control window which is perfectly displayed now in yyyy/mm/dd (ISO standard), removing any ambiguity.

I didn't spot where the data range limiter was in the code when I was glancing through it.  I'm guessing you haven't had a chance to tweak it up to that.  No issue.  If you do and care to share the next version with me, I'll be glad to use it; just flip me a copy through the system.

I'll close this off, with sincere thanks.  First rate help and follow-through.
0
 

Author Closing Comment

by:qeng
ID: 40625062
Martin, super stuff.  Thanks a bunch.

Sorry I missed your question about whether it was ok for the user to enter a date from the following week.  In my application that would be necessary since I will be using this for users to be able to enter future meeting dates in spreadsheets as well as to enter dates for prior activities or documents (which don't have the right metadata to mine).

So in my application, I'd expect to go into the code and adjust an 'earliest_allowable_date' and a 'latest_allowable_date' and trigger an informative error if someone tried to enter a date outside of that date range (i wouldn't bother to trigger an instruction as it would get to me a nuisance each time they use it, unless it showed up only on the first entry).

This will allow me to 'guide' the delivery of some materials which have deadlines associated with them where some dates which are too far in the future wouldn't be useable.  Similarly, the same logic could apply not to let users apply dates which preceded some formal kick-off date in project work or something to that effect.

Thanks again for the first rate support.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now