Solved: Calendar control woes

[mvidas]

Hi All,

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

Can someone do me a favor and run this code?[vba]Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
End Sub[/vba]Assuming 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:[vba]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[/vba]
Any help/thoughts/confirmations would be appreciated
Matt

[Philcjr]

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.

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

[mvidas]

That's perfect! Simply adding ActiveSheet.Select did the trick

Thanks!

[vba]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[/vba]

[johnske]

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.

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

Ditto Matt,[vba]Sub scc()
ActiveSheet.OLEObjects.Add ClassType:="MSCAL.Calendar", Left:=75, Top:=65, _
Width:=225, Height:=150
ActiveSheet.Select
End Sub[/vba]works fine...