PDA

View Full Version : shrinking calendar



wellssh1
09-09-2006, 08:59 PM
Hello,

I have a spreadsheet with many cells that require a date. I have added calendar control 11.0 and running it with the code below. The problem is, that everytime the sheet is opened, the calendar gradually gets smaller and smaller each time the workbook is opened. I have tried resizing through formatting the object, but after opening the workbook several times, it's has shrunk down to unusable again.



Private Sub Calendar1_Click()
ActiveCell.Value = CDbl(Calendar1.Value)
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Select
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("B161:B184,I161:I184,P161: P184,"), Target) Is Nothing Then
Calendar1.Left = Target.Left
Calendar1.Top = Target.Top + Target.Height
Calendar1.Visible = True
' select Today's date in the Calendar
Calendar1.Value = Date
ElseIf Calendar1.Visible Then Calendar1.Visible = False
End If
End Sub


any ideas ?

thanks in advance

johnske
09-09-2006, 11:23 PM
Set the calendars size when the sheet is activated e.g. Private Sub Worksheet_Activate()
With Calendar1
.Height = 200
.Width = 250
End With
End Sub