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

"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

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

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

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?

"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

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

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

"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

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

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

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


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

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.

"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