VBA Calendar Control without Control

  • The files that Phil e-mailed me (version 6, version 7, and version 8) are all available by going to the downloads link from the Brandtrock files link in the signature area of this post.


    Going to Excel-IT is more worthwhile if you want to gain additional insight into Excel, but the files are on my downloads page as a courtesy as well.:grin:

  • Quote

    Originally posted by royUK
    Phil's excellent Calendar Control is available for free download here


    Roy, The link on your site for the calendar download brings you straight back here.... doesn't appear to be a file ?


    Will

  • Updated version


    Latest updated version of the Pop-up Calendar.


    Revision includes, new month indexing when a date for a preceding or proceeding month is selected on a given calendar. Additionally, the coloring and look/feel have been improved.


    Any comments or suggestions, welcomed.


    Enjoy,


    Phil


    (see attachment)

    Files

    • Calendar9.xls

      (97.79 kB, downloaded 191 times, last: )
  • Hi Phil,


    The version 8 is a real help ...just downloaded 9 and there seems to be a small hi-cup - clicking on Show Calendaran there is an error message ...


    The macro "attachment.php?attachmentid=91!Show_Cal' cannot be found.


    Similar with the user form example ..Show_Calendar cannot be found.


    Have I missed something?



    Thanks

    [SIZE=6]S[/SIZE][SIZE=6]t[/SIZE][SIZE=6]u[/SIZE] :drum:

  • Phil, I actually expanded on this a while ago and made my own version based on yours. With this latest version I get all Jans.....that's because it uses the american date format. The one I made took this into account and detected the Local. I also added a class module to do away with the button clicks.
    When I have finished this I plan to place this up on my site with CREDIT to you of course. Good going ! :rock:

  • Ivan,


    How do you stand on your revised version of the calendar pop-up?


    I'm really interested to see how you have improved on the original.


    Best regards,



    Phil

  • Phil -



    Not sure if this will suit your needs, but I've just posted this to the forum.



    Check it out!
    http://ozgrid.com/forum/showthread.php?t=22194



    I created this in response to your first post. Sorry it took me so long to get a working model of what I thought you were looking for.



    Let me know if you find it useful.

  • Wow...


    It's amazing how much it looks like the one that I had developed.
    (for reference see attached)


    I do however like the code in your version.


    Thanks for the effort.


    Phil

    Files

    • Calendar9.xls

      (97.79 kB, downloaded 1,052 times, last: )
  • Minor problem with Phil's Calendar version 9


    Any comments on the following problem?


    I have been using Phil's Calendar calendar. It has been working great.

    Background:
    I have a customised XL workbook with several sheets. The user uses a different sheet every month to insert data including dates in three different cells. They need to insert a date for the beginning and end period. This is programmed to be done by "double clicking" the particular cell. They also have a choice to insert by typing (with the right format of course).

    Latest Problem:
    In three cases (all different), the users have ran into this problem:

    Apparently, the calendar does pop up but the months show up as all "January". Even if they change the year (for example) the months show up as " January" (all 12 months).
    They were advised to get around this problem by typing the date. It has worked.

    I am sharing this info to provide you feedback and also am curious as to what is causing this problem.

    1. when a calendar is opened, and the drop-down month feature is used, all the months listed are January -- 12 times. If any one of them is selected, January appears on the sheet. If "Today" is selected, "August" appears as the month (and I assume whatever month it is today). The other features of the calendar, date and year work ok.
    2. Operating System is Windows XP Professional, Ver. 5.1 2600xpsp2.030422-1633 Service Pack 1.
    3. Excel is Excel 2002 10.5815.4219 SP-2
    4. Computer is Dell OPTIPLEX 9X270, Pentium 4, 3 gHZ, 2GB RAM
    5. The calendar in the identical spreadsheet run on my computer works fine.

  • Hey Gops/Squire King,


    I think Squire King is right...I have heard other responses like this one in the past and they have related to the UK date format.


    I cannot however duplicate the problem on my machine even when I change to UK date formats, thus I cannot repair the issue. I do feel it's in the code and seem to recall someone else getting around the problem. I believe it was when we could not attach workbooks to the ozgrid site and I don't believe I got the revisions. If I had I would have worked it into my calendar.


    If someone out there has resolved this, please attached it to this thread and I'll update my latest version and reattach it for everyone’s use.



    Best regards to all.


    Phil

  • Phil-


    Have you tried to use the dateserial function. I also made a calendar form and have came accross the same issues. I'm attempting to use the dateserial function so that the international settings do not matter.


    I haven't had a chance to finish my updating and post my results but I believe that will fix the issue.



    Regards

  • Re: VBA Calendar Control without Control - January fix


    Here's a fix for the issue with all months = January in Phil's Calendar date picker code. (PHIL - THANK YOU! I've been looking for an easy solution for this without having to load .ocx files on everyone's computer - our company has several versions of Windows and Office in use at any given time).

    First, you can duplicate the problem by selecting a language other than English in Control Panel>Regional and Language Settings (I selected French (France), then loading the module.


    The Fix:
    In module CalendarFrm>Sub UserForm_Initialize, replace the line:
    CB_Mth.AddItem Format((i) & "/1/" & (ThisYear), "mmmm")
    with:
    CB_Mth.AddItem Format(DateValue(ThisYear & "-" & CStr(i) & "-01"), "mmmm")


    I used the Year-Month-Day form for DateValue, because I'm pretty sure it's Month and Day are always recognized correctly (other formats may be sensitive to the Date format settings in Regional and Language settings). I'm pretty sure that the problem was caused by the fact that some people define the default date format in terms of dd-mm-yy (everywhere but USA) and others use mm-dd-yy.


    I also added the line CB_Mth.Clear above the For/Next loop that loads CB_Mth (I wasn't seeing too many months being loaded, I just like to make sure).


    - Steve

  • Re: Phil's VBA Calendar - January / UK Version fix




    Hi All,
    Looks as though Steve was right....


    When using Phil's Calendar in a non USA environment (when regional settings on your computer are set to a date format that is not mm/dd/yy) Phil's Calendar (v10) will only show the month of January.


    The attached file now incorporates Steve's ideas into a file.
    IE the attached "Calendar10.2.xls" will work for computers outside of the USA.


    I have also fiddled (I say fiddled as i'm not brilliant, so if theres a better way that would be great) with code a bit as a new problem cropped up. When selecting a date, if the day was between 1 & 12 the calendar read the day as a month and switched the calendar display to that month.


    EG. Select month of June, then select date as 15/06/05 & calendar continues to display June as month.... all OK.


    Select 11/06/05 and calendar switches to November. (My examples written in dd/mm/yy format)


    Hope this is helpful for all... i wanted to work on this to iron out issues as Phil's Calendar is my favourite, simply because of the way it looks....


    Cheers,



    Chuck.



    ## Administrators, it looks as though Ozgrid originally had all calendars together in one thread... ("Hey Thats Cool!")? Could we look at removing the old long long threads, and replace them with 1 post with all the different variations of calendar so that all the most upto-date versions are in one place ?

  • Re: VBA Calendar Control without Control


    Hey Chuck.


    I was using Phil's calendar until I ran into the "january problem". Then I used IWRK4depr's version (which was fixed for both European and N American date setting) at this URL:
    http://www.ozgrid.com/forum/showthread.php


    With this version, however, I also came across the problem that you mentioned:


    "a new problem cropped up. When selecting a date, if the day was between 1 & 12 the calendar read the day as a month and switched the calendar display to that month.


    EG. Select month of June, then select date as 15/06/05 & calendar continues to display June as month.... all OK.


    Select 11/06/05 and calendar switches to November. (My examples written in dd/mm/yy format)".


    Can you please tell me how you fixed this latest problem. I have a choice of going back to Phil's calendar that you updated (calendar 10.2) or get the fix from you to add to the IWRK4depr's version that I am using now.


    thanks
    Gops