modify size of calendar control

[JMay]

The below code (supplied by Ron de Bruin) is working fine, except for
the following:

When in Design Mode I see the Full month Calendar (Sunday - Saturday),
but
When I exit out of Design mode and click on cell B10 say, I only see
Sunday thru Friday (Saturday is truncated). I've tried back in Design
mode
Increasing the width, but without success. How can I modify to be able
To view the fully needed width?

TIA,
Jim May


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' Data starts on Row 7
If Target.Row >= 6 Then
Select Case Target.Column
Case 2 'applies to Column B
Calendar7.Left = Target.Left + Target.Width -
Calendar7.Width
Calendar7.Top = Target.Top + Target.Height
Calendar7.Visible = True
Case Else: If Calendar7.Visible Then Calendar7.Visible = False
End Select
Else: If Calendar7.Visible Then Calendar7.Visible = False
End If
End Sub

 

[Ron de Bruin]

Hi JMay

Do you change the zoom of the worksheet ?

 

[JMay]

Ron:
As a matter of fact, YES -- I need 85% to see all my stuff (10 columns)
at one glance. Is this a problem? I found your code on google --it's
great,,
Jim

 

[Ron de Bruin]

Hi Jim

I think that you changed it to 85 after you insert the control

Delete the control
Set zoom to 85 if it is different
insert object to add the control

 

[JMay]

Thanks Ron; I got it working great!!

Two other questions before moving on..

When I drop the calendar control again on the worksheet I notice in the
formula bar:
=EMBED("MSCAL.Calendar.7","") << it was also .7 the first time I did
it.

Why the ".7") ??

I had also previously changed the code from Calendar1 to Calendar7 and
had changed the control's name property (in the property window) from
Calendar1 to Calendar7 since at the time the Calendar1 didn't seem to
work..

I'm a bit confused at this point, but everything is working,,

Can you add anything that will help me?

Thanks again,

Jim May

 

[JMay]

Also Ron:

If I end up with 1,000 entries in my Col B (using the calendar control)
Does this impact my PC's memory or Filesize? If not I also have dates
In Column C where I'd like to engage the same control -- all I'd have
To do (I think) is change the Select Case statement to test for either
2 OR 3
Select Case Target.Column <<

Right?

Jim

 

[Ron de Bruin]

=EMBED("MSCAL.Calendar.7","")
Excel 2007 ???

In Excel 2003 you not see a number
I bug this in the 2007 beta

I had also previously changed the code from Calendar1 to Calendar7 and
had changed the control's name property (in the property window) from
Calendar1 to Calendar7 since at the time the Calendar1 didn't seem to
work..

If you change the name to Calendar7 then you must change all Calendar1's in the code to Calendar7
http://www.rondebruin.nl/calendar.htm

I will add a with statment in the code this week then it is easier to change it.

 

[Ron de Bruin]

Hi JMay

Does this impact my PC's memory or Filesize?

No you only use one control


Or this, you not want the control to popup when you select a header I think

If Not Application.Intersect(Range("B2:C1000"), Target) Is Nothing Then

 

[JMay]

I am strictly xl2003.

One last q:

If when I click on B10 for example and I want the Calendar control
To appear but drop-down (an inch or so) and to the right (an inch or
so),
Meaning not immediately under the activecell, how would I modify your
Code?

Thanks so much for mentoring us?

Jim

 

[JMay]

Thanks -- Here's my FINAL Code (partial) - thanks to you..

' Data starts on Row 7
If Target.Row >= 6 Then
Select Case Target.Column
Case 2 'applies to Column B
Calendar1.Left = Target.Left + Target.Width -
Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
Case 3 'applies to Column C
Calendar1.Left = Target.Left + Target.Width -
Calendar1.Width
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True

 

[Ron de Bruin]

Play with this

Calendar1.Left = Target.Offset(0, 5).Left + Target.Width - Calendar1.Width
Calendar1.Top = Target.Offset(2, 0).Top + Target.Height

You can also fill in a number for left

Calendar1.Left = Target.Left + 100