Posts by Ramblin

Re: Non-activex Datepicker Calendar Control

Got it

Thanks for the help Baodad

The attached file is like the others - complete with instructions - with the following update:

When you click on a cell that is formatted with d-mmm-yy, the Pop-Up-Calendar will show (as before) but the year/month that is displayed will depend on what is already in the cell clicked on
- if the cell is empty, just like now, the current month is displayed with Today's date highlighted
- if the cell has a date already entered into it, the month/year of that date will be displayed
with the date already entered into the cell highlighted.

This was done by changing the Code in the FormPicker
from

Private Sub UserForm_Initialize()
    Set clsCal.BoundForm = Me
    clsCal.LoadView
End Sub

to

Private Sub UserForm_Initialize()
    Dim ExistDate As Date
    Set clsCal.BoundForm = Me
    If ActiveCell.Value = 0 Then
        clsCal.LoadView
    Else
        ExistDate = ActiveCell.Value
        clsCal.LoadView (ExistDate)
    End If
End Sub

and changing the (numerous) if statements in the ClsCalendar LoadView Sub
from

If dtTemp = dtToday Then

to

If dtTemp = dtDate Then

Thanks again Baodad for all your initial work and subsequent help!

Ramblin

Re: Non-activex Datepicker Calendar Control

Baodad,

I am trying to decipher the code to adapt one thing, but it is not something I can readily get a handle on.

Is it an easy fix to have the code set up such that if the cell being clicked on (which has the d-mmm-yy format) already has a date entered into it, the pop-up appears with that date already selected, instead of today's date? The pop-up should still come with today's date selected if there is no date in the cell being clicked on.

RIchard

Re: Non-activex Datepicker Calendar Control

Good catch Baodad

Attached is the updated version of the file incorporating the instructions and the code from Baodad.

Ramblin

Re: Non-activex Datepicker Calendar Control

Baodad

You are great!

After asking you a couple of questions about this, I discovered this post was in a forum with the instruction "Do not ask any questions". Well, you did answer, and guided me to a way to adapt this to have what I wanted. Thank you.

I have attached, for you, and anyone else that wants to get the benefit of Baodad's good work (and my 2% adaptation), an Excel Workbook, with easy step-by-step instructions (for newbies like me) describing how to make this work in any other Excel 2010 Workbook.

In the attached version, a couple of adaptations to Baodad's original:
1) Instead of double-clicking any cell to bring up the Pop-Up Calendar, pre-format to "d-mmm-yy", using a Custom Format, any cell for which you want a pop-up calendar to appear when the cell is clicked on (single clicked).
2) The code is set at the Workbook level, not the Worksheet level, so any additional sheets aded to the workbook will automatically have this capability as well.

Thanks again to Baodad for solving a problem caused by Microsoft's change from Excel 2007 to 2010 which prevented the previous pop-up calendar that worked in Excel 2007 to crash in 2010.

I have tested this and it works back to Excel 2003.

Long live open source community!

Ramblin

Re: Non-activex Datepicker Calendar Control

If anyone else wants to benefit from the great work done, you can download this file which includes the code created and has step-by-step instructions for how to duplicate it (for newbies like me)

Re: Non-activex Datepicker Calendar Control

Baodad,

Is there a way to adapt the code so that instead of double-clicking on (any) cell, the pop-up (user) form only displays when you single-click on a cell that has been pre-formatted with a specific (date) format like d-mmm-yy ??

Ramblin

Re: Non-activex Datepicker Calendar Control

Baodad

I was able to create a new Workbook and bring all your code/forms over to make this work

thank you very much

One question / ask :
I see that the code in the "Microsoft Excel Object" is in the worksheet, not the workbook. This means I copy/paste the code for each worksheet I add to the workbook. I tried copying the code into the Workbook holder, but it did not work. Is there a simple change I could make to the code so I could put it into the Workbook holder and thereby have it available for any worksheet in the workbook?

Great work and thanks again.

Ramblin

Re: Non-activex Datepicker Calendar Control

Bingo

That is what I am looking for

And by embedding it in the Sample Worksheet, you showed it can be sent to someone who can use it without setting up their system first - just enable macros.

I'll look into this more on the weekend (I think it will take a while for me to grasp this) so thank you very much

Richard

Re: Non-activex Datepicker Calendar Control

I like what I see and I much prefer this to solutions where I need to have the PC have certain files resident in the Programs folder. Using your way, (I assume) I can create a spreadsheet, send it to someone else, and it will work on their PC without them having to anything with their PC to enable it?

I may be asking too much here, and if this forum is only for the seriously advanced user, then I guess I am out of luck, but if you are willing, do you have step-by-step instructions for how to implement this solution?

I got the sample file and like the look/feel. I would want to have the calendar pop-up whenever I selected, say, a Cell with a date formatted as d-mmm-yy (for example).

Richard