Announcement

Collapse
No announcement yet.

Popup Calendar for Excel VBA 2010

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Popup Calendar for Excel VBA 2010

    My company just cutover to Microsoft Excel 2010 - and, of course, my ActiveX popup calendar no longer works. I've been digging through a number of threads, and there are a fair amount of solutions offered - all of which involve exporting a form and then importing that form into Excel. My problem is that I cannot bring any executable code into my corporate system - I can copy VBA code all day long, but not a form executable. So - what I need to understand is either a) whether there is a 2010 function or ActiveX control that functions like the Calendar control in 2007 or, b) some detailed instructions on building a calendar form that could then be used independently of Active X. The following thread: Non-activex Datepicker Calendar Control has exactly what I'm looking for - but I was unable to recreate the Form so that the VBA code could run against it. Is that possible? Are there instructions anywhere that could walk me through how to regenerate the form? Thanks all.

  • #2
    Re: Popup Calendar for Excel VBA 2010

    Some alternatives that i use here
    Hope that Helps

    Roy

    New users should read the Forum Rules before posting

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

    Comment


    • #3
      Re: Popup Calendar for Excel VBA 2010

      Roy - sorry, that site is blocked on my network - other suggestions?

      Comment


      • #4
        Re: Popup Calendar for Excel VBA 2010

        can't see why it should be blocked.
        Hope that Helps

        Roy

        New users should read the Forum Rules before posting

        For free Excel tools & articles visit my web site

        If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

        RoyUK's Web Site

        royUK's Database Form

        Where to paste code from the Forum

        About me.

        Comment


        • #5
          Re: Popup Calendar for Excel VBA 2010

          it is - can't get there from here.

          Comment


          • #6
            Re: Popup Calendar for Excel VBA 2010

            It must be your IT department for some reason
            Hope that Helps

            Roy

            New users should read the Forum Rules before posting

            For free Excel tools & articles visit my web site

            If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

            RoyUK's Web Site

            royUK's Database Form

            Where to paste code from the Forum

            About me.

            Comment


            • #7
              Re: Popup Calendar for Excel VBA 2010

              Roy (and all) - after much digging, prodding, poking and general banging of my head against the wall, I found some open source code at: https://sites.google.com/site/e90e50...-control-class that I was actually able to modify and make run in my environment (btw - yes, my IT dept had the site you recommended blocked). I had to do some minor modifications to get it to work, but now I have a calendar independent of "almost" all ActiveX. The one sticker is that I have to create a user form as well as a command button. However....after I made the modifications, I enabled writing of a date from a calendar into the active cell, but I can't fire off the actual calendar from the command button. This is probably really simple, but can you give some advice on what I'm doing wrong here? In actuality, I would rather not have a command button at all - do you think I inserted something that I didn't need to? I'm going to add the code blocks - hopefully I'll use the add buttons correctly - as well as the actual spreadsheet. I appreciate any advice - thanks in advance. NOTE: I believe I've discovered the size limits on this forum - the cCalendar code is available from the other site. Hopefully I'll be able to load my source code later.

              Code that goes into the UserForm code box
              Code:
              Private WithEvents Calendar1 As cCalendar
              
              Private Sub UserForm_Initialize()
              
              Set Calendar1 = New cCalendar
              
              Calendar1.Add_Calendar_into_Frame Me.Frame1
              
              End Sub
              
              Private Sub Calendar1_DblClick()
              
              ActiveCell.Value = Calendar1.Value
              Unload Me
              
              End Sub
              
              Private Sub Calendar1_Click()
              
              ActiveCell.Value = Calendar1.Value
              Unload Me
              
              End Sub
              
              Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
              
              Set Calendar1 = Nothing
              
              End Sub
              Code that goes into the worksheet

              Code:
              Private WithEvents Calendar1 As cCalendar
              
              Private Sub UserForm_Initialize()
              
              Set Calendar1 = New cCalendar
              
              Calendar1.Add_Calendar_into_Frame Me.Frame1
              
              End Sub
              
              Private Sub Calendar1_DblClick()
              
              ActiveCell.Value = Calendar1.Value
              Unload Me
              
              End Sub
              
              Private Sub Calendar1_Click()
              
              ActiveCell.Value = Calendar1.Value
              Unload Me
              
              End Sub
              
              Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
              
              Set Calendar1 = Nothing
              
              End Sub
              Code in module CalendarModule

              Code:
              Option Explicit
              
              Sub nonActive_cCalendar()
              
              DatePicker.Show
              
              End Sub

              Comment


              • #8
                Re: Popup Calendar for Excel VBA 2010

                Aha - I think I've located the attachments function - my source code should be here.Test_calendar.xlsm

                Comment


                • #9
                  Re: Popup Calendar for Excel VBA 2010

                  I was mistaken on the command button - you don't need it after all. I am curious why it doesn't work, but not enough to mess around with it. I did run across another interesting problem: while pulling the Calendar 12.0 ActiveX object out of my code, I began experiencing a Microsoft Forms 2.0 Object Library error - the only way to get rid of this was to open up Design Mode while in the debugger - this kicked off the References window. If anyone could tell me a better way to display the object references, I would greatly appreciate it. The other difficulty that I'm having is positioning the userframe - it pops up in the middle of the spreadsheet, rather than next to the cell. Some guidance on this would be appreciated as well - I succeeded in repositioning the calendar inside the frame, but not the actual popup box on the worksheet. Also - is there a way that I can make the popup calendar disappear when the TAB key is pressed? I can't seem to find an event handler for that - does one exist? Thanks in advance.

                  Comment


                  • #10
                    Re: Popup Calendar for Excel VBA 2010

                    I am going to self close this one out - see http://www.ozgrid.com/forum/showthre...035#post610035 for the solution to the positioning of the userform - but it would be great if anyone could tell me how to capture a TAB entry into a userform (so I could just turn the form off).

                    Comment

                    Working...
                    X