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.
"RobN" wrote:
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
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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.
"RobN" wrote:
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
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message
...
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
"RobN" wrote:
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