Chip Pearson's VB Object references

[Rodels]

I recently distributed a 2007 workbook that contained pop up calendars
courtesy of the Microsoft Office Calendar Control 2007. I foolishly assumed
that when a user opened the workbook, Excel would “realise†that there was a
Calendar control embedded and automatically enable the VB reference.
Optimistic eh?
I was looking at ways to automate the enablement of the Calendar reference
object – I found this code on Chip Pearson’s site and wondered if a similar
thing could be run to automatically select the Calendar control? This code
enables the Microsoft Visual Basic Application Extensibility 5.3 object
library.

ThisWorkbook.VBProject.References.AddFromGuid _
GUID:="{0002E157-0000-0000-C000-000000000046}", _
Major:=5, Minor:=3

All help appreciated!
Kind regards,
Robert


Rodels! Creating weapons of Maths Destruction since 2003.

 

[Bob Phillips]

Surely, if you created the workbook in 2007, and your other users also have
2007, it will already be referenced (did you check the References list?).
More probable is that they don't have the control on their machine.

 

[Jan Karel Pieterse]

Hi Rodels,

I recently distributed a 2007 workbook that contained pop up calendars
courtesy of the Microsoft Office Calendar Control 2007. I foolishly assumed
that when a user opened the workbook, Excel would “realise” that there was a
Calendar control embedded and automatically enable the VB reference.
Optimistic eh?

Problem is, there is NO control embedded in your file, only a reference to the
control on your system.

If the user does not have that control installed, there is no way to have VBA
register the control, simply because it isn't there in the first place.

You need to instruct your user how to download and install the mscal.ocx
control (available through the Microsoft website).

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

 

[Rodels]

Bob and Jan Karel,
many thanks.

Bob you are correct - the reason the control would not activate is that the
control was not available. Jan Karel's suggestion of downloading the ocx and
installing that worked. I suspect that environments with locked down secure
desktops could pose a problem to this approach so I will need to rethink.

Many thanks again!

Robert
--
Kind regards,
Robert


Rodels! Creating weapons of Maths Destruction since 2003.

 

[Bob Phillips]

That is always a problem, that is why many developers develop their own
calendars.

 

[Rodels]

Interesting point.

By the way, I enjoyed your sessions at the Excel User group last year.
--
Kind regards,
Robert


Rodels! Creating weapons of Maths Destruction since 2003.

 

[Bob Phillips]

Thank-you, I am sorry there are none planned for this year, but they take a
lot of organising.