Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

Non-activex Datepicker Calendar Control

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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:

    Code:
    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

  • #2
    Re: Non-activex Datepicker Calendar Control

    Good on ya

    Comment


    • #3
      Re: Non-activex Datepicker Calendar Control

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

      Ger

      Check out our new reputation system. Click on the "star" under the post!
      _______________________________________________

      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

      _______________________________________________

      Comment


      • #4
        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 | Excel Matters Rory | Kris' Spreadsheet Solutions | Domenic xl-central | SO The Macro Man | The Smallman

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #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

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      Re: Non-activex Datepicker Calendar Control

                      Thanks Ramblin.

                      Check out our new reputation system. Click on the "star" under the post!
                      _______________________________________________

                      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

                      _______________________________________________

                      Comment


                      • #12
                        Re: Non-activex Datepicker Calendar Control

                        The only reason to have code in a worksheet is to take advantage of the events that are available there. Same goes for workbooks. I'm attaching a screen shot that shows how you can browse through the events available to you depending on what object you've clicked on in "Microsoft Excel Object," shown in the tree in the left pane.

                        Click image for larger version

Name:	shot.jpg
Views:	1
Size:	31.3 KB
ID:	1096492

                        Once you click on an available event, the VBA editor will automatically insert the starting code you need. If there's an event that looks like it will work for you at the workbook level rather than the worksheet level, try it out. You can play with these events by putting simple code like
                        Code:
                        msgbox "Event fired!"
                        in one of the event subs it generates for you. Then go back to Excel and play around to see if you can make the code fire. (A message box should appear).

                        If you find yourself needing to put duplicate code in many worksheets, the best practice is to remove as much common code as you can and put it in a code module. Then call that code from the worksheet event code.

                        Comment


                        • #13
                          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
                          Attached Files

                          Comment


                          • #14
                            Re: Non-activex Datepicker Calendar Control

                            Great work!

                            I can confirm that your workbook works in my Excel XP (2002) at home, even after file conversion from the .xlsm format.

                            One regression in the code from the very first file I uploaded, is that the "v" character to the right of the year in the calendar should actually be rendered in the "Marlett" font, where it will be displayed as an up and down arrow. If you click the top half of the character, it increases the year, and vice versa.

                            You can fix this by changing this property in the calendar class module:
                            Code:
                            Public Property Set BoundForm(myUserform As Object)   'tie the class to a specific form
                            'On Error GoTo Catch
                                Set mForm = myUserform
                                With mForm
                                    With .LabelUpDown
                                        With .Font
                                            .Name = "Marlett"
                                            .Size = 11
                                            .Charset = 2
                                        End With
                                        .Caption = "v"
                                    End With
                                End With
                            Finally:
                                Exit Property
                            Catch:
                                Stop
                                Resume
                            End Property

                            Comment


                            • #15
                              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
                              Attached Files

                              Comment

                              Trending

                              Collapse

                              • cayusbonus
                                From week numbers to standard date format
                                cayusbonus
                                Hi all, I am quite new in VBA, but after two weeks I have learnt a lot thanks to this forum, so I feel kind of in debt with it. I have been recently looking for an example where VBA converts from week dates to the regular mm/dd/yyyy date format, but I couldn't find a satisfactory answer. Yes, we have the weeknum function to be used in a spreadsheet but so far I couldn't call it in my VBA code. So therefore, I generated the code below that applies the ISO 8601 standard to make such a conversion. Hope it helps to somebody.


                                Code:
                                Sub WeeksToDates()
                                'Converting YYWW.DD to MM/DD/YYYY dates according ISO 8601. More info https://en.wikipedia.org/wiki/ISO_week_date
                                Dim Todayf As String        'Date in yywk.dd format
                                Dim Yearf As Integer        'Year of the input date
                                ...
                                1 day ago
                              • Reafidy
                                Textbox Class Object
                                Reafidy
                                Hi All,

                                I recently had a request for a better method of controlling multiple textboxes on a userform. "I have lots of textboxes on a userform I want the user to only be able to enter numbers in all of them!"

                                The solution: Making use of a class module. The method below will alow you to handle the event for all textboxs at once without having to add events for each individual textbox on the form.

                                Add a class module and name it "clsObjHandler"

                                Code:
                                Option Explicit
                                Private WithEvents tbxCustom1 As MSForms.TextBox    'Custom Textbox
                                Public Property Set Control(tbxNew As MSForms.TextBox)
                                    Set tbxCustom1 = tbxNew
                                End Property
                                Private Sub tbxCustom1_Change()
                                'Message Box To Display
                                ...
                                December 11th, 2007, 05:32
                              • Furybringerx
                                Parsing HTML Table to Excel
                                Furybringerx
                                My personal project this past week has been trying to parse a HTML table to Excel.
                                I've used many resources to finally get the code I'm looking for. It has taken a week to figure it out so I feel that I should share it to help others like me.

                                Code:
                                Sub ParseTable()
                                Dim IE As InternetExplorer
                                Dim htmldoc As MSHTML.IHTMLDocument 'Document object
                                Dim eleColtr As MSHTML.IHTMLElementCollection 'Element collection for tr tags
                                Dim eleColtd As MSHTML.IHTMLElementCollection 'Element collection for td tags
                                Dim eleRow As MSHTML.IHTMLElement 'Row elements
                                Dim eleCol As MSHTML.IHTMLElement 'Column elements
                                Dim ieURL As String 'URL
                                
                                'Open InternetExplorer
                                Set IE = CreateObject("InternetExplorer.Application")
                                IE.Visible
                                ...
                                December 11th, 2013, 05:39
                              Working...
                              X