Add Calendar Multiple Times To Excel

I need to add today's date drop down in about 100 cells. Each cell needs to be separate from all the others (no linking the same date).

I have added the Microsoft Date and Time Picker via Developer Options once and linked it to a cell, but can't cut and paste it to other cells... So, that means I have to manually add each calendar and manually edit the cell linkage? That would take a very long time.

Is there a way to add multiple calendars and have Excel 'auto-adjust' the calendar to each cell?

Or maybe I need to ask it this way:
How can I quickly add a date selection drop down to over 100 cells at one time?
LVL 6
Paul WagnerPrincipal ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Please see my Magical floating ActiveX control article. It will describe exactly how to do what you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Tsake a look at my examples here. They do not use ActiveX controls so will work on any users computer without the problems associated with ActiveX controls that may not be present on all machines.
Martin LissOlder than dirtCommented:
Here's a working example. It is currently set up to show the date picker in cells A1, B5 and C2 via this line
Set rngDP = Union(Range("A1"), Range("B5"), Range("C2"))

Open in new window

and that can easily be changed to include any number of contiguous and/or non-contiguous cells.
28926462.xlsm
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Paul WagnerPrincipal ConsultantAuthor Commented:
@Martin Liss
I have your xlsm file open and am reading your article. Where do I enter/copy the code to have it be in the other empty cells so the calendar is there?
Martin LissOlder than dirtCommented:
Please see the workbook I posted in post ID: 41466693
Paul WagnerPrincipal ConsultantAuthor Commented:
OK. How do I search by post ID?
Martin LissOlder than dirtCommented:
download
Roy CoxGroup Finance ManagerCommented:
pAUL

hAVE YOU LOOKED AT MY ADDIN? tHE OTHER EXAMPLES HAVE FULLY WORKING EXAMPLES WITH THE CODE TO SHOW THE CALENDAR ON A SHEET OR EVEN A uSERfORM
Paul WagnerPrincipal ConsultantAuthor Commented:
(Gotcha. I was looking for another article.)

So, I changed the range in your xlsm document and that worked fine, allowing me to mass add several calendars.

I then copied and pasted your VB code from your worksheet to mine and got these errors.
2.PNG
3.PNG
Martin LissOlder than dirtCommented:
In my article I mention that you need to manually add one control to the sheet. So if you haven't already done so, add a datepicker to the sheet where you want to use it.
Paul WagnerPrincipal ConsultantAuthor Commented:
After manually adding a date picker to the sheet, I get this error and the word Union is highlighted:

error 2
Martin LissOlder than dirtCommented:
Union is meant join together two or more sub-ranges and you only indicated one range. If in fact that is the only range you want to use then replace your Worksheet_SelectionChange code with this version.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim ws As Worksheet
'Dim rngDP As Range
Set ws = ActiveSheet

On Error Resume Next
grngCurrent.Value = DTPicker1.Value

' Set the range where you want the date picker to appear
'Set rngDP = Union(Range("A1"), Range("B5"), Range("C2"))
'If Intersect(ActiveCell, Range(rngDP.Address)) Is Nothing Then
If Intersect(ActiveCell, Range("D13:D34")) Is Nothing Then
    DTPicker1.Visible = False
    Exit Sub
End If

Set grngCurrent = ActiveCell

Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If

With DTPicker1
    .Font.Size = 8
    .Visible = True
    .Left = Target.Left + 1
    .Top = Target.Top + 1
    If Target.Width >= 65 Then
        .Width = Target.Width - 1
    Else
        .Width = 65
    End If
    .Height = Target.Height - 1
    .Text = Target.Value
    .Activate
End With

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True

End Sub

Open in new window

Paul WagnerPrincipal ConsultantAuthor Commented:
OK, I fixed it. I changed the code to this:

Set rngDP = Range("D13:D34")

That did the trick. Every cell now has a calendar in it!
Now, I'll expand that range to the entire column and see how it goes.
Thanks for the help.
Martin LissOlder than dirtCommented:
While that apparently worked for you, please see the code in my last post for a different way to do it that doesn't need rngDP. (See line 13)
Paul WagnerPrincipal ConsultantAuthor Commented:
Got it. Now, how do I delete it if I want to clear the cell?
Paul WagnerPrincipal ConsultantAuthor Commented:
I'll give you an A since you worked back and forth with me (which I greatly appreciate), but you might consider improving your article. As I am not a VB or Excel guru, I was completely lost when reading your instructions. If you can receive this, I recommend screenshots in numbered order with brief instructions for each step. Yes, it will take some more work, but you will get less follow-up questions. Food for thought. Thanks again for your help!
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help and I will consider your advice.

In my profile you'll find links to some other articles I've written that may interest you.

Marty - MVP 2009 to 2015
              Experts Exchange MVE 2015
              Experts-Exchange Top Expert Visual Basic Classic 2012 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.