 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / General Excel Questions / May 2007Calendar code not working
I have the following code to bring up the Calendar control. When I converted the excel workbook from XP version to 2007 version on my home computer it worked fine, but when I open it on my office computer I get a message saying "Object required". When I open the older version on my office computer, the Calendar code works OK. Can you please also advise what code I need to control the size of the Calendar? Rob Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Cells.Count > 1 Then Exit Sub If Not Application.Intersect(Range("H2"), Target) Is Nothing Then Calendar3.Left = Target.Left + Target.Width * 2 - Calendar3.Width Calendar3.Top = Target.Top + Target.Height * 7.5 Calendar3.Visible = True ' select Today's date in the Calendar Calendar3.Value = Date ElseIf Calendar3.Visible Then Calendar3.Visible = False End If End Sub My first thought is that your Calendar control may have changed names during the copy. Right click the calendar control and choose Properties and see what the control's name is. If other than Calendar3, then change code to get it to work. If you can't see it or get to it for this, read on... But since it works at home, and not at work - it could be a library problem. You may have a calendar that is created with XP version of the calendar control and it may not exist on your office computer. You may need to simply delete the calendar in the office file copy and create a new one based on the calendar control available on that computer. Another option is to make a copy of the mscal.ocx file on your home machine and make it available on your office system. On my system, the file is located at c:\Program Files\Microsoft Office\OFFICE11\MSCAL.OCX > I have the following code to bring up the Calendar control. When I > converted the excel workbook from XP version to 2007 version on my home [quoted text clipped - 17 lines] > End If > End Sub Thankyou for your reply. 1. I can't right click on the control as it doesn't ever appear (I just get the message saying "Object required".) When I right click on the previous version's control, where it does work, nothing happens. ie no menu allowing me to select properties. 2. How do I delete the existing (supposedly faulty) Calendar control? I'm not even sure how to go about creating a new control in 2007. Do I select (in the Developer Tab) Insert, then the CalsControl Control option from the More Controls option. When I try that I get a message (even on a blank new workbook) that I cannot Insert Object! 3. I do have the mscal.ocx file on my office computer. Should I really replace that with the XP versions mscal.ocx file from my home computer? Or should I rename, etc. I'm sorry, but even though I created the Calendar Control in XP, I don't really understand what's happening, particularly now that 2007 is so much different. Rob > My first thought is that your Calendar control may have changed names > during [quoted text clipped - 37 lines] >> End If >> End Sub RobN, First - NO! don't overwrite either copy of your mscal.ocx control on either machine. If we get into messing with copying them from one to the other, use the rename trick to temporarily take one of them out of the picture. We want to be able to bring both systems back to their current state even if we don't succeed in getting things working at the office. Let's hold off on trying anything like that for the moment - I don't want to tell you to do something that may make things worse. #1. To be able to right click on the calendar control and see its properties, you need to be in design mode. Open up the Controls Toolbox and you'll see the little Triangle, Ruler and Pencil icon at the upper left. That takes you in and out of design mode. #2. I think we need to find out why you cannot Insert Object in 2007. To insert one into a new book in Excel 2007, you are generally correct: Developer Tab -> Insert | More Controls and select the Calendar 12 control and click [OK] then your cursor should change to a very thin + mark and basically you click and drag to create a rectangle on the sheet that is about the size you want for the calendar. When you release the mouse button the calendar control should appear and you should be able to resize it. That's the way it's supposed to work - or at least the way it worked for me here. #3 - in theory, since there is a copy of mscal.ocx on the machine at the office, you should NOT have to place an older copy (from home system) onto that machine. The copy on the office machine should be a later version and it should be backward compatible with earlier version of the calendar control. I'm thinking (could be wrong) that the these two issues are related: that the workbook with calendar from home that works doesn't work at the office, and that you aren't permitted to insert a calendar control into a brand new workbook on the system at the office in 2007. Problem is, I honestly don't know where to begin to look for the answer to that riddle right now. Going to have to ask around and do some research. I'll start that process now. If you don't hear back shortly, it's not that I've abandonded you, it's just that I don't have an answer yet. > Thankyou for your reply. > [quoted text clipped - 60 lines] > >> End If > >> End Sub Thankyou so much for your replies!! I really appreciate the time this is taking for you to help me with this problem. I had tried to insert the Calendar by being in design mode. In fact, it automatically turns on the design mode when I try to insert such an object. When I try to insert the "CalControl Control" (which I notice you call "Calendar 12 control" - is that a problem? Maybe I'm trying to insert the wrong thing??), the formula bar says =EMBED("CALEDARUI.CalControlsCtrl.1","), but the error message appears. I did try to insert other objects from the More Controls section and had no problems with some, whereas I do have the same error message with others. I don't know if that is related to my Calendar problem but I don't know what most of those objects in that rather long list are, and maybe they shouldn't be ones you can insert??? The Calendar control really confuses me as, where does it go when you do insert it? I presume it's hidden within the sheet somehow and you need to call it up as I have in the VB code. How do you know how many such Calendar Objects have been inserted into a sheet. Can the ones you don't use be deleted somehow? When I open the old xls version of the file within Version 2007, I can now access the Calendar and its properties, etc., as I forgot that the sheet was protected. However, unprotecting the sheet in the Vs2007 file doesn't help. Rob > RobN, > First - NO! don't overwrite either copy of your mscal.ocx control on [quoted text clipped - 125 lines] >> >> End If >> >> End Sub You've already covered on area that I was going to ask about: that of either the workbook or worksheet being protected (you also checked the workbook, I presume). Now, we come up with a real difference between the way things seem to be working in your copy of Excel 2007 and mine. When I go to [Developer] tab and Insert controls, in the ActiveX controls I have listed "Calendar Control 12.0" and the formula I'm shown is =EMBED("MSCAL.Calendar.7","") and once I 'draw' it on the sheet, it is fully visible and ready for me to adjust it's properties by right-clicking on it and choosing Properties. So far, no takers in my request for advice/assistance on this elsewhere, so I'm kind of left with the thought that any others that have read it are kind of staring blankly and scratching their heads also at this point in time. What are the chances of you attaching this misbehaving file to an email and sending it to me at (remove spaces) HelpFrom @ jlatham site.com If nothing else, perhaps I can get a calendar control into it here and send it back and see how it looks or acts on your system if I can. If it's a very large file, perhaps even .zip it up and attach it? I could look at it this evening if you can. > Thankyou so much for your replies!! I really appreciate the time this is > taking for you to help me with this problem. [quoted text clipped - 153 lines] > >> >> End If > >> >> End Sub Hi J, I re-looked at your previous advice and because you said your Embeded name for the Calendar control was different to mine I wondered if I had some problems with Excel itself. I should have done this straight away, but because I'd only recently installed Office 2007 at work, it didn't occur to me. I chose to "Repair" the installation and the Calendar control now works fine. I really appreciate your help which certainly led me to a solution for my problems and trust I haven't been an unnecessary burden or you feel that it's been a time wasting effort! Rob > You've already covered on area that I was going to ask about: that of > either [quoted text clipped - 228 lines] >> >> >> End If >> >> >> End Sub A last minute thought. You said you'd converted this file from XP format to 2007 format on your home system. Have you tried taking the XP .xls file to your office and then converting it to 2007 format on that machine? > Thankyou for your reply. > [quoted text clipped - 60 lines] > >> End If > >> End Sub Yes, I had thought of that, but I have made MANY changes to the file since conversion and the time to do that again would be too to great to cope with. Rob >A last minute thought. You said you'd converted this file from XP format >to [quoted text clipped - 81 lines] >> >> End If >> >> End Sub Rob, Very good news to hear that a Repair Installation of Office 2007 cleared up the problem. It was about to make me crazy! And very good of you to post back with the solution. Even better of you not to have just stood around waiting for an answer to be handed to you and continued to think and dig around and work towards a solution on your own. Time wasted? Not a moment - what research and effort I did put out all goes toward getting a little smarter in some areas that I had to dig into. > I have the following code to bring up the Calendar control. When I > converted the excel workbook from XP version to 2007 version on my home [quoted text clipped - 17 lines] > End If > End Sub |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.