Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Popup Calendar for Excel VBA 2010

  1. #1
    Join Date
    27th March 2012
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,298

    Re: Popup Calendar for Excel VBA 2010

    Some alternatives that i use here
    Hope that Helps

    Roy

    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.

  3. #3
    Join Date
    27th March 2012
    Posts
    13

    Re: Popup Calendar for Excel VBA 2010

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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,298

    Re: Popup Calendar for Excel VBA 2010

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

    Roy

    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.

  5. #5
    Join Date
    27th March 2012
    Posts
    13

    Re: Popup Calendar for Excel VBA 2010

    it is - can't get there from here.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,298

    Re: Popup Calendar for Excel VBA 2010

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

    Roy

    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.

  7. #7
    Join Date
    27th March 2012
    Posts
    13

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

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

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

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    27th March 2012
    Posts
    13

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    27th March 2012
    Posts
    13

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    27th March 2012
    Posts
    13

    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).

    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. calendar controls in excel 2010 forms
    By Shirl@SMC in forum EXCEL HELP
    Replies: 5
    Last Post: May 31st, 2012, 14:46
  2. Popup Calendar
    By lokedog in forum EXCEL HELP
    Replies: 2
    Last Post: May 23rd, 2012, 08:59
  3. Replies: 2
    Last Post: May 8th, 2011, 05:45
  4. Calendar Popup
    By kdnichols in forum EXCEL HELP
    Replies: 1
    Last Post: March 15th, 2007, 07:51
  5. calendar popup (can you get it to stay up)
    By bwychopen in forum EXCEL HELP
    Replies: 8
    Last Post: June 23rd, 2005, 01:54

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