what type is calendar control

[c1802362]

running Excel 2003 (going to 2007 in the next few months)

I have inserted a pop-up calendar (Insert/Object.../Calendar Control
11.0). No problems getting it to do what I want - except when I
declare my variables (Option Explicit).

I can't seem to determine what type the Calendar object is - Dim
Calendar as Object returns a "Block Not Set" error.

Any sugegstions?

Art

 

[Rick Rothstein]

You put this calendar control directly on the worksheet, not a UserForm,
correct? I think you can do it this way...

Dim MyCalendar As Object
Set MyCalendar = Worksheets("Sheet2").OLEObjects("Calendar1").Object
MsgBox MyCalendar.Value

 

[Bernie Deitrick]

Art,

This works for me:

Private Sub Calendar1_Click()
Dim myCal As Calendar
Set myCal = Calendar1
MsgBox myCal.Value
End Sub


HTH,
Bernie
MS Excel MVP

 

[c1802362]

You put this calendar control directly on the worksheet, not a UserForm,
correct? I think you can do it this way...

Dim MyCalendar As Object
Set MyCalendar = Worksheets("Sheet2").OLEObjects("Calendar1").Object
MsgBox MyCalendar.Value

yes - the code puts it directly on the page when the user clicks in
the referenced cell

Art

 

[c1802362]

Art,

This works for me:

Private Sub Calendar1_Click()
Dim myCal As Calendar
Set myCal = Calendar1
MsgBox myCal.Value
End Sub

HTH,
Bernie
MS Excel MVP

I'll try this, but when I started typing my Dim statement (Dim
Calendar1 As ....) I couldn't get an object description with Calender
in it

(Which is why I asked the question in the first place - thanks!)

 

[Bernie Deitrick]

Once you add the calendar to your sheet, double click it, and then the
calendar click event code window will open and you can type

Dim Calendar1 As Cal

and the Calendar object type will be available in the autocomplete of the
declaration.

Bernie

Art,

This works for me:

Private Sub Calendar1_Click()
Dim myCal As Calendar
Set myCal = Calendar1
MsgBox myCal.Value
End Sub

HTH,
Bernie
MS Excel MVP

I'll try this, but when I started typing my Dim statement (Dim
Calendar1 As ....) I couldn't get an object description with Calender
in it

(Which is why I asked the question in the first place - thanks!)

 

[Bernie Deitrick]

And I forgot to add that inserting a calendar control automatically adds a
reference to the calendar control in your VBA project.

Bernie

Art,

This works for me:

Private Sub Calendar1_Click()
Dim myCal As Calendar
Set myCal = Calendar1
MsgBox myCal.Value
End Sub

HTH,
Bernie
MS Excel MVP

I'll try this, but when I started typing my Dim statement (Dim
Calendar1 As ....) I couldn't get an object description with Calender
in it

(Which is why I asked the question in the first place - thanks!)