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").Objec t
MsgBox MyCalendar.Value

--
Rick (MVP - Excel)


"c1802362" wrote in message
...
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

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" wrote in message
...
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

On Nov 16, 10:56*am, "Rick Rothstein"
wrote:
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").Objec t
MsgBox MyCalendar.Value

--
Rick (MVP - Excel)

"c1802362" wrote in message

...



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

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

Art

On Nov 16, 10:56*am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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

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


"c1802362" wrote in message
...
On Nov 16, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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

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


"c1802362" wrote in message
...
On Nov 16, 10:56 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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" wrote in message

...



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

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