PDA

View Full Version : Solved: Calendar control woes



mvidas
01-19-2006, 07:34 AM
Hi All,

Having a bit of a problem while using the cal control programmatically.

Can someone do me a favor and run this code?Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
End SubAssuming design mode is off when it is run, it does add a calendar control to the sheet but it is not interactive. If you turn on design mode then turn it back off, it becomes interactive (and you can see it change color slightly when interactive).

Any ideas what I can do? I left the version off the cal control on purpose as it will probably be run on different machines.

Truth be told, it is for a selection change event, see:Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) <> "D5" Then Exit Sub
Application.EnableEvents = False
With ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150)
.Name = "CalControl1"
End With
Application.EnableEvents = True
End Sub
Private Sub CalControl1_Click()
Range("D5").Value = CalControl1.Value
Shapes("CalControl1").Delete
End Sub
Any help/thoughts/confirmations would be appreciated :)
Matt

Philcjr
01-19-2006, 08:10 AM
Matt,

I am no guru, but I tried your code and I could not select the calendar as well.

I then tried to select another sheet tab and then go back to select the calendar, and it worked then.

So I just added one line of code and I was able to select the calendar right after it was created.

Hope this can give you some idea or direction to go.


Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
Worksheets("Sheet1").Select
End Sub

mvidas
01-19-2006, 08:16 AM
That's perfect! Simply adding ActiveSheet.Select did the trick ;)

Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address(0, 0) <> "D5" Then Exit Sub
Application.EnableEvents = False
With ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150)
.Name = "CalControl1"
End With
ActiveSheet.Select
Application.EnableEvents = True
End Sub
Private Sub CalControl1_Click()
Range("D5").Value = CalControl1.Value
Shapes("CalControl1").Delete
End Sub

johnske
01-19-2006, 08:22 AM
Matt,

I am no guru, but I tried your code and I could not select the calendar as well.

I then tried to select another sheet tab and then go back to select the calendar, and it worked then.

So I just added one line of code and I was able to select the calendar right after it was created.

Hope this can give you some idea or direction to go.


Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
Worksheets("Sheet1").Select
End Sub
Ditto Matt,Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
ActiveSheet.Select
End Subworks fine...