Excel 2007 - Active X controls change to shapes

[ging18]

Morning All,

After many happy years running office 2000 its been decided that its time to move to office 2007. I now have the enjoyable task of going through all our workbooks ensure everything still work...

Some of the Excel 2000 workbooks have sheets that contain VBA Controls. These are linked directly to various macros. EG Sheet1.label1 = "example". This works great in 2000.

In excel 2007 I have replaced the controls with ActiveX controls. This works great until I close the workbook.

When the workbook opens the controls appear to be just shapes, rather than the activeX control. If I add new activeX controls the macros all work again. But each time the workbook closes the ActiveX/VBA type properties disappear.

Am I missing something obvious? Is there something I should be doing?

As an example, create a new "Macro-Enabled" workbook. Add an activex button control. Click it a few times and it behaves like a button should. Save the workbook, reopen it and try clicking it again. At this point I get nothing, its just a shape.

Any help would be appriciated as I've got lots of heavily marco'd books to update.

Thank you kindly
Christian

 

[ging18]

I've identified the problem, just need a work around now...

The issue is caused because the ActiveX security settings are set to "Disable all controls without notification".

This is a really useful feature, which I have no control over changing as the "network security" have disabled all the options...

So, I think i'm going to have to create each of the required controls when the workbook opens.

Adding the control should be easy enough

ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False _
        , DisplayAsIcon:=False, Left:=265.5, Top:=51.75, Width:=229.5, Height _
        :=118.5)

Then give it the correct name to link back to the vba code...