Non-activex Datepicker Calendar Control

Re: Non-activex Datepicker Calendar Control

Stay well clear of Merged Cells Mr Fuller. Nothing but trouble in the long run.

See: http://www.officearticles.com/…ls_in_microsoft_excel.htm

They are the devils own invention. ::D

If you need to use them, then there is something fundamentally wrong with the design of your sheet. You should use Centre Across Selection instead.

Cheers,
Ger

Re: Non-activex Datepicker Calendar Control

Wow, if only most software devs worked as well as you do!

Many thanks, tested on Windows 7 SP1 64bit with Office 2010, and also Windows XP SP3 with Office 2007 and works perfectly!

Thank you !

Re: Non-activex Datepicker Calendar Control

Hi Ger Plante,

Unfortunately it is a spreadsheet that has been handed down over millenia from accountant to accountant that have their ways set in concrete

Cheers!

Re: Non-activex Datepicker Calendar Control

Well...after a bit more testing I put in a teeny bit more error checking code into the ProcessEvent routine - it could trip up if a date that was centred across a selection happened to be in the very last column on the spreadsheet (an unlikely scenario but still....).

Also, I noticed I had forgotten to put back the sheet protection after I had finished the last recoding.

So a slightly modified version is attached. Again, only the Sheet1 code has been changed.

Enjoy!

Re: Non-activex Datepicker Calendar Control

Could this be modified to work on specific cell range? Say AZ69:AZ77 and cell d7?

Re: Non-activex Datepicker Calendar Control

Anything is possible in VBA! As always there would be a number of different ways to implement this.
Can I assume you are referring to the current activesheet only?
Also, is it likely that the range of cells will vary over time?
I would suggest one way to tackle it would be to have named ranges with a common prefix eg, "DATERNG" so your named ranges could be "DATERNG01", DATERNG02", "DATERNG03" etc. That way, the code could see if the activecell is in one of the named ranges and you can change the named ranges any time. The code could then loop through the named ranges using the prefix until it runs out of ranges to check.

Re: Non-activex Datepicker Calendar Control

As suggested to wsautrey1000, an updated version of the DatePicker demo using named ranges is attached. I have created a named variable called POPUPTYPE which controls the behaviour of the pop-up calendar on the activesheet.

The values that POPUPTYPE can take are:
0 : means any cell with a date value on the worksheet can activate the pop-up calendar (this was the default behaviour previously and is the current value of POPUPTYPE in the demo).

A value of 1 or 2 means only cells in the named range/s can activate the pop-up calendar.
In addition:
1 : means any cell with a date value on the worksheet can activate the pop-up calendar.
2 : means any empty cell or cell with a date value on the worksheet can activate the pop-up calendar. There is a caveat here - the empty cell cannot be part of a range that is centred across a selection.
There can be up to 26 named ranges which can pop-up the calendar being POPUPA through to POPUPZ.

Any other numeric value for POPUPTYPE will turn-off the pop-up calendar. If the named variable POPUPTYPE does not exist, the pop-up calendar will also not activate.

As there are multiple worksheets in this demo, the code to deal with the pop-up calendar on the activesheet has been moved into a new module called SheetPop. In addition, you can now specify a minimum and maximum date to the pop-up calendar at the worksheet level and you can specify if the calendar starts on a Sunday (Monday by default). Look at the comments in the Sheet1 or Sheet2 code.

So in the wsautrey100 specific example: named range POPUPA would be set to AZ69:AZ77 and named range POPUPB would be set to cell D7 on the activesheet. The value for the named variable POPUPTYPE would be either 1 or 2 (depending on how you want to handle empty cells).

Update 22-Apr-11: Sorry folks, I noticed that the blurb on the "Range tester" sheet referred to named ranges starting with "DATERNG" instead of "POPUP". I have updated just the wording on this sheet to avoid confusion, there have been no code changes. "DATERNG" was the original prefix I was going to use.

Re: Non-activex Datepicker Calendar Control

After a bit more playing around with the code, I noticed that the pop-up may fail using the named range option if the named ranges were not "contiguous" eg, if you defined a POPUPA and a POPUPC range but not a POPUPB range.
So, yet again, a slightly modified version is attached with an updated SheetPop module.

Re: Non-activex Datepicker Calendar Control

If you investigated the new code in Sheet1 and Sheet2, you may have noticed (aside from the fact it is identical) that I created a "shell" routine to deal with the calendar pop-up called ProcessPopUp.

The idea was that each of the triggers for popping up the calendar should call this routine (left click, right click, double click, etc). That way, you could specify a min date, max date and whether the calendar starts on a Sunday all in one place for each worksheet.

However, I missed updating the Worksheet_BeforeDoubleClick event code - it is still calling the HandleSheetPopup event directly instead. So, to use this correctly, the Sheet1 and Sheet2 code for this event should look like:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Cancel = True
  [I]ProcessPopUp[/I] Target
End Sub 'Worksheet_BeforeDoubleClick

I have attached a version with this correction and also showing an example of how you can use named variables to limit the date range of the pop-up calendar on a sheet. The Range tester sheet (Sheet2) has some code in the ProcessPopUp routine to do this.

Of course, there are numerous ways to store the min and max date including global variables or dates read from a worksheet somewhere or even a calculated value...this is just one example of how to deal with it.

Re: Non-activex Datepicker Calendar Control

Quote

Chip Pearson's form positioner code is too old to contend with Office 2007/2010 so I kludged together some changes that seem to work although I cannot guarantee this in all cases -

Have reworked it a little to position the form offset fromthe mouse pointer - this was so it could be used on a userform when a button is clicked. The same logic works perfectly well on a worksheet as well... While I have the greatest admiration for Mr Pearson, rather than a complete module with a whole bunch of extra code, this is about 25 lines in total.

Would there be a call for that code here...?

Re: Non-activex Datepicker Calendar Control

Why not

Re: Non-activex Datepicker Calendar Control

I'd love to see it.

My current projects using the date picker only need to use the drop-down calendar on a UserForm. I incorporated Chip Pearson's Form Positioner into the demo just to show how the calendar could work relative to the active cell on the worksheet but it may be overkill if you have something that can achieve the same result in far fewer lines of code.

Re: Non-activex Datepicker Calendar Control

cytop, one question based on how this might be implemented:

Quote

Have reworked it a little to position the form offset fromthe mouse pointer

If this is meant to also work on the worksheet, what would happen if the activecell was moved using just the arrow keys and not the mouse?

Or is the intention to only display the pop-up if the mouse is clicked on the activecell?

Re: Non-activex Datepicker Calendar Control

One of the reasons I keep reading this forum is that I never stop learning!

In the back of my mind, I knew the duplicated code I had on Sheet1 and Sheet2 in the last DatePicker demo could be combined but I never really investigated it - mainly because it gave the programmer some scope to set a min and max date range for each sheet if required.

However, having seen a response by mikerickson just today to a question on the Help forum, the lightbulb suddenly lit up. Of course! The code for all sheets in the workbook could be stored in the ThisWorkbook module. And any code that needs to modify the behaviour of the pop-up calendar in a particular sheet could be handled by the ProcessPopUp routine as it could be passed the sheet name.

Whilst I was at it, I did some investigating of named "arrays" as I thought it would be easier to store a min/max date pair into an array, especially if multiple min/max date pairs were required. So, after more hunting on the ozgrid forums, a few more changes sorted this out as well. And, yes, although Excel does not make it easy to type in or modify a named constant or array, it means changes can be made outside of the macro code.

So in the attached version, the code for Sheet1 and Sheet2 has been "moved" (with modifications) to the ThisWorkbook module and there's been a slight tweak to the SheetPop module to do with how the named constant POPUPTYPE was referenced.

Re: Non-activex Datepicker Calendar Control

If your projects are anything like mine, I need the datepicker functionality that was provided by the DTPicker control only on a UserForm (ie, not on any of the worksheets). As this functionality is common to a number of my projects, I thought I'd investigate putting my "calendar code" into an Excel Add-In rather than duplicating the code for each project.

My first attempt failed as the code in my project couldn't reference the code in the userform in my add-in. After more research, I found an elegant way around this with minimal modification to code in my existing projects. Essentially, each project declares a generic public 'Calendar' object in a module somewhere and that object is set in ThisWorkBook in the Workbook_Open procedure. The trick to remember is not to unload the Calendar object anywhere.

So, having built the add-in the only additional step is to make a reference to the add-in for each project that requires the date picker functionality. I found a great little article on doing this here:
http://www.exceltip.com/st/How…Functions_in_VBA/630.html

As you will see in that article, it is not even necessary to have the add-in loaded in Excel, you just need to add a reference to it for each project that requires it. You can do that by browsing to the .xla file in the VBE (Tools --> References --> Browse).

The project file and add-in can be placed in the same folder or the add-in can be copied to the folder where your other add-ins reside. Read the note in that article above called About Distributing Your Files.

My calendar add-in is attached as is a demo file that shows you how you can call the functions in the add-in on a UserForm. Remember that you will need to set the reference to the add-in in the demo code to link it to the location where you placed the add-in.

I am in the process of testing this on an application that has many UserForms and multiple instances of a calendar field on some of those UserForms and so far this is successful as long as the Calendar object is not unloaded or 'reset' in any way.

Re: Non-activex Datepicker Calendar Control

I downloaded this file but then realised that you can right click on the tool box to add more controls - from here you can select the Microsoft Date Picker. It's still not the same as the old version but seems to be as good as a solution as any other available on the web at the moment and it uses the slightly neater calendar control.

Re: Non-activex Datepicker Calendar Control

Thanks Richard, I believe the purpose of this thread and code is to provide a Non-ActiveX, Non-MSCOMCT2.OCX dependent date picker calendar control for VBA userforms...

Frequently OCX files can go missing (yes even MS OCX files!) and/or can be updated and this breaks code/applications that were developed and dependant on those ocx files. It can be a headache to assume/manage users OCX files, so quite often, as in this case, the easiest thing to do is just create your own non-ActiveX control

Ger

Re: Non-activex Datepicker Calendar Control

Could anabody tell me how the AktiveX objekt in excel 2010 called "RCMDateTimeCtrl1" works?!
I really like the design but I don't know how I could get the date I picking linked to a cell? Anybody?!

Re: Non-activex Datepicker Calendar Control

Hi Kehlet - welcome to the forum - this isnt the right forum for asking this question... please post your question in the Excel Help forum, where someone can take a look at your query.

Best Regards
Ger

Re: Non-activex Datepicker Calendar Control

Ramblin -
I too am a newbie to Excel 2010 - - I benefitted greatly from your step-by-step instructions. You put a lot of effort