Cannot create object error in Excel 2003

[Jack]

I'm getting the error: "Cannot create object" when instantiating a form
commandbutton in Excel 2003 with VBA code. The code works fine in XL2007. I
suppose also woth noting is that the XL07 is running under Vista while XL03
is running under WinXP Pro with the latest SP.

The command that fails is as follows:

wksPhotos.OLEObjects.Add ClassType:="Forms.CommandButton.1"

wlsPhotos is the active worksheet and the statement above is contained
within it.

I've moved the code to module with no change in results.

Any ideas?

 

[papou]

Hello Jack
If the code is in the sheet module then try:
Me.OLEObjects.Add ClassType:="Forms.CommandButton.1"

Or else in a standard module:
Dim wksPhotos As Worksheet
Set wksPhotos = ThisWorkbook.Worksheets("Photos")
wksPhotos.OLEObjects.Add ClassType:="Forms.CommandButton.1"

Both methods tested successfully in Excel 2003 and Windows XP

HTH
Cordially
Pascal

 

[Jack]

No luck. Neither worked for me.

Thanks so much for your time!

--
Jack
<><>

Hello Jack
If the code is in the sheet module then try:
Me.OLEObjects.Add ClassType:="Forms.CommandButton.1"

Or else in a standard module:
Dim wksPhotos As Worksheet
Set wksPhotos = ThisWorkbook.Worksheets("Photos")
wksPhotos.OLEObjects.Add ClassType:="Forms.CommandButton.1"

Both methods tested successfully in Excel 2003 and Windows XP

HTH
Cordially
Pascal

 

[papou]

Jack
Did you amend the second option with the relevant sheet name?
Is the sheet protected?
Is the workbook saved in Excel 2003 format?

Apart from that, I can't see so far any other reason why it should not work.
As mentioned, I tested on my pc with Win XP and Excel 2003 and it worked.
Can't test with Xl2007 and Vista.

HTH
Cordially
Pascal

No luck. Neither worked for me.

Thanks so much for your time!

 

[Jack]

Excellent questions.

Yes. I've tried using the specific sheet name, wksPhotos, as in my code
snipet in my original message. I've also tried

Worksheets(5).OleObjects.Add...,
ActiveWorksheet.OLEObjects.Add...
With wksPhotos
.OLEObjects.Add
. . .
End With

By the time this statement executes, the sheet is unprotected. The command:

wksPhotos.Unprotect Password:=sPassword

works fine. No object reference errors. An if pause execution after the
statement and double check the worksheet, it is unprotected.

Yes, with worksheet is saved as an Excel 2003 worksheet.

The odd thing is that this code used to work in XL2003 on this same XP
system. Since posting my initial questions I've attempted to use some of the
worksheets that did work only to find they no longer work as well. I've
rebooted the system, no change. This leads me to believe it might be some
setting in Excel. Hoowever, I've even tried the worksheet on other XP Pro
systems with XL2003 and the same thing happens.

I've also saved the worksheet as an XL2007 Macro Enabled worsheet, closed
Excel on my Vista system, reopened the spreadsheet and resaved it in Excel
2003 format. I did get a couple compatibility warnings, but they related to a
different worksheet and involved conditional formatting.

I'm 99.99% positive the code is right. I'm thinking now that this is
actually the side effect of some other issue. Perhaps my only recourse is to
start from scratch and rebuild the applicaiton.

Any other thoughts would be appreciated. Thanks again for your consideration!

Regards,

 

[Jack]

FWIW...I resolved the problem. By copying all my code, without the procedure
name, into the clipboard, deleting the procedure, saving the worksheet,
recreating the procedure and inserting my old procedure code into the new
procedure and saving, the OLEObjects.Add invocation worked. Go figure.