Some alternatives that i use here
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.
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 that goes into the worksheetVB: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 CalendarModuleVB: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
VB:Option Explicit Sub nonActive_cCalendar() DatePicker.Show End Sub
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.
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).
There are currently 1 users browsing this thread. (0 members and 1 guests)