| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
modify size of calendar control
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
Hi JMay
Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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 "Ron de Bruin" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
=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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 "Ron de Bruin" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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 "Ron de Bruin" wrote in message : =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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 "Ron de Bruin" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
modify size of calendar control
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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 "Ron de Bruin" wrote in message : =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. -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 "Ron de Bruin" wrote in message : 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 -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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" wrote in message : Hi JMay Do you change the zoom of the worksheet ? -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "JMay" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Calendar control size trouble! | Excel Worksheet Functions | |||
| Calendar control font size | Excel Discussion (Misc queries) | |||
| Modify Yearly Calendar to Monthly Calendar Excel 2000? | Excel Programming | |||
| Control disappeared problem - specifically, the Calendar Control | Excel Programming | |||
| Calendar Control: Can't exit design mode because control can't be created | Excel Programming | |||
Linear Mode
