PDA

View Full Version : Cannot Insert Object



Shred Dude
12-02-2010, 05:56 PM
I'm stumped on this one.

I have a workbook that uses DatePicker and MonthView controls from the MSCOMCT2.OCX class. All is well on every machine I run it on accept one, and of course this turns out to be the notebook of the guy paying the bills!

On his MacBook, running Windows7 in Dual boot mode, with Excel 2010, I can only seem to access the DatePicker or MonthView control when it is encapsulated in a UserForm, in any workbook, not just the one I've created.

I successfully registered MSCOMCT2.OCX with regsvr32 (as administrator) on this machine to even get to this point.

Now, my workbook operates OK on that machine where those controls are in a userform, but not where they are embedded on a sheet. Further investigation reveals that the VBA project doesn't compile on this machine, citing inabilities to locate the referenced control in the worksheet code modules.

On this same machine, if I start a new workbook, and attempt to insert a Datepicker or Monthview control directly onto a worksheet, I get a "Cannot Insert Control" error window. However, if I go into the VBE and create a userform for this new workbook, I can add both controls to the UserForm and run the UserForm error free.

The fact that the controls even appear in the Additional Controls selection window makes me believe they are successfully registered. Furthermore, I can use them within userforms. So, what's preventing me from using them on the Worksheet layer, on this particular machine???

I've never had this one before. Anyone have any ideas?

Thanks,

Shred

mikerickson
12-02-2010, 06:25 PM
You can use conditional compilation to distinguish between the two machines.
Where are you creating this control? On a UF or on a sheet.

If on a UF, then you could add a UserForm to that copy of the workbook.
If on a sheet, you could put a control on a hidden workbook to use as a template for copy/pasting rather than inserting the needed control.

While I do use a Mac, I have no experience with using Excel on a dual mode Windows platform.

Shred Dude
12-02-2010, 08:35 PM
Thanks for the reply.

The issue seems not to be with just my specific workbook, but with any workbook on this particular machine.

If I manually open a new workbook on this machine I can't manually insert a DatePciker or Monthview control on a sheet, even though those items appear in the additional controls dialog.

As for my workbook, it has an embedded MonthView control on a couple of sheets, and a userform with three DatePicker's in it. The userform works fine on that machine, but the worksheets bomb when the monthview code is reached in Selection Change events for example. Again, only on that machine...works great everywhere else.

I haven't tried to programatically add a MonthView control to the sheet at runtime when needed. I might give that a try.

I'm not sure I'm following your suggestion on conditional compilation. A little more info on my end would be helpful. The intended use of this workbook is for it to reside on a server. Individuals would access it as required, accepting a read-only copy if necessary, to view the data contained therein.

My limited understanding of conditional compilation is that it relies on a constant established at the VBA Project Level that you would then query at runtime (#IF ...) to make decisions from.

In this scenario, the MacBook user wouldn't have a unique copy of the workbook on their notebook that they use, they'd be accessing the file stored on the server that many users access. As such, I'm not clear on how I'd take advantage of a pre-esatblished constant. Am I missing something there?


One of the offending sheets merely uses the embedded MonthView control as a dropdown when certain cells are entered. I guess I could redesign that to use an appropriately positioned userform containing the MonthView Control.

The other offending sheet however uses the MonthView control in a two-month view to facilitate highlighting a range of dates which then triggers a refresh of an adjacent pivottable. With that one, the Control shows up on the MacBook when the sheet is activated, along with a Compile Error message box. So the MacBook user gets to see the control, but can't change any dates on it.

I guess you could code a modeless userform to contain the monthview control but it wouldn't look as good as having the control embedded on the sheet IMO.

Any ideas most welcome. Again, it all works as is on every other users' machine. Just this one MacBook can't handle the MonthView control embedded on a sheet. Other MSForms controls can be added to sheets on the MacBook by the way. Something just doesn't add up!

mikerickson
12-02-2010, 09:54 PM
I'm not experienced enough with that operating environment to be us much help.

But, if your shared file is written to work on a Mac, it will work on the PC's. If it was anyone else's machine.. but on the other hand, if the boss uses a Mac, others will follow.

My advice is "re-write the workbook for a Mac environment" (which perhaps shows how valuable my opinion is in this situation.)