Popup Calendar for Excel VBA 2010

[wileydk]

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.

[royUK]

Some alternatives that i use here

[wileydk]

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

[royUK]

can't see why it should be blocked.

[wileydk]

it is - can't get there from here.

[royUK]

It must be your IT department for some reason

[wileydk]

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

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

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

Option Explicit 
 
Sub nonActive_cCalendar() 
     
    DatePicker.Show 
     
End Sub 

[wileydk]

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

[wileydk]

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.

[wileydk]

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