Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 7 1 2 3 5 ... LastLast
Results 1 to 10 of 63

Thread: Non-activex Datepicker Calendar Control

  1. #1
    Join Date
    20th March 2010
    Posts
    8

    Non-activex Datepicker Calendar Control

    All of you have been so helpful to me here over the years that I have been learning Excel and VBA. I finally feel like I can give something back to the community.

    Attached is a Non-ActiveX, Non MSCOMCT2.OCX dependent date picker calendar control for VBA userforms. To incorporate it into your project, you would need to copy the clsCalendar class module, and also add all the calendar control objects inside the FrameCalendar frame on the userform (including the frame itself) onto your form. You would also need another control on the form somewhere to be bound to the class, which would receive the user's selected date. Mine is called "TextBoxDate" and it is set in the UserForm code like this:

    VB:
    Private Sub UserForm_Initialize() 
        Set clsCal = New clsCalendar 
        Set clsCal.Form(Me.TextBoxDate) = Me 
    End Sub 
    
    
    Anyway, I hope it works for you, or at least it can give you ideas. I hope it's appropriate but I'm sharing this under terms of the "New BSD license," which is really flexible. I don't really mind what you do as long as no one takes this and sells it and pretends they came up with it.

    I developed this in 64-bit Excel 2010 beta, on 64-bit Windows 7.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,718

  3. #3
    Join Date
    16th June 2005
    Location
    Dublin
    Posts
    4,553

    Re: Non-activex Datepicker Calendar Control

    well done... looks good. Thanks for sharing.

    Ger
    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont.

    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25...

    The BEST Lookup function of all time

    Dynamic Named Ranges are your bestest friend

    _______________________________________________

  4. #4
    Join Date
    9th April 2007
    Location
    Alstonville, Australia
    Posts
    3,596

    Re: Non-activex Datepicker Calendar Control

    Wow thats a keeper. simply brilliant
    If the solution helped please donate to RSPCA

    Sites worth visiting: Rabbitohs | Excel-it royUK | Golden Rules | Forum Rules | Freebie Stuff | Smallman Freebies

    Ozgrid Free Excel Training

  5. #5
    Join Date
    24th August 2010
    Posts
    9

    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

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    20th March 2010
    Posts
    8

    Re: Non-activex Datepicker Calendar Control

    Hmmm....

    This control depends on the functionality of VBA's userforms, so it really needs to be placed in a userform.

    In pre-Office 2007 versions of Excel, we could use a lot of tricks and APIs to remove a userform's title and borders, and make it look almost like it wasn't a userform, but it still needs to be a userform.

    I'm attaching a sample Office 2007-2010 macro-enabled workbook that demonstrates the behavior you desired. But it turns out to be a little complicated to implement, as you will see when you look through the code. You need 1) code in the worksheet itself to bring up the date picker, 2) a userform with the date picker controls on it, 3) the clsCalendar class module code, and 4) another normal module just to hold a public variable so that all the other code modules can access it.

    Unfortunately, this is a fairly advanced solution and it takes some understanding to suit it to your purposes. But I was once a beginner too, and I remember all the hours I spent trying to understand someone else's code. It was so helpful to me, I hope I can pass on the favor.

    Here's the sample workbook: Sample2.xlsm

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th August 2010
    Posts
    9

    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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    24th August 2010
    Posts
    9

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    24th August 2010
    Posts
    9

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    24th August 2010
    Posts
    9

    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)
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 2
    Last Post: August 30th, 2006, 17:07
  2. ActiveX Calendar Form
    By chathabox in forum EXCEL HELP
    Replies: 2
    Last Post: July 6th, 2006, 21:45
  3. Replies: 3
    Last Post: August 1st, 2004, 12:26
  4. Calendar Pop Up WITHOUT using ActiveX
    By Squire_King in forum EXCEL HELP
    Replies: 5
    Last Post: June 8th, 2004, 17:42
  5. Creating ActiveX Calendar Control
    By sergv in forum EXCEL HELP
    Replies: 2
    Last Post: September 12th, 2003, 18:08

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno