what type is calendar control

  • Thread starter Thread starter c1802362
  • Start date Start date
C

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
 
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
 
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
 
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
Click to expand...

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

Art
 
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
Click to expand...

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!)
 
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
Click to expand...

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!)
 
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
Click to expand...

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!)
 
Back
Top