Excel - Form/ActiveX Control Properties Not Available

0
votes

In Excel 2010, is there a setting that prevents a user from being able to access the properties of a form or ActiveX control?

I am working with a user who can open up an Excel file and insert controls (both form and ActiveX) on a worksheet. However, when that user clicks any object and goes to it's properties, the properties for the sheet are displayed instead of the properties for the selected object.

Additionally, any macros that attempt to access the control through the sheet throw an "Object doesn't support this property or method" error, because the control is null. See the example below that attempts to reference a dialog control named "CommonDialog1" on a worksheet named "AddParts":

Worksheets("AddParts").CommonDialog1.ShowOpen

I am able to run this exact same macro on other computers from the same workbook without any issues. Is there some user setting in Excel that is preventing the user from being able to access objects?

2
exceptionexcelvba
have you tried protecting workbook elements? – user2140173
Is ActiveX controls enabled/trusted by the users Office? Also, is the user on a Mac? ActiveX as far as I'm aware is only supported in a Microsoft Environment – StoriKnow
In general first check whether activex controls are properly enabled which can be done by selecting officebutton on top left in that exceloptions->TrustCenter->TrustCenterSettings->ActiveXSettings->Enable all ActivexControls without restrictions make sure this is selected and then go to developer tab then insert activex controls and then do right click to open particular object properties. – 565

2 Answers

0
votes

To answer the above questions: 1) The workbook is unprotected 2) Enable all ActiveXControls without restrictions is checked. However, I was able to work around this issue by using the built-in Application.GetOpenFilename VBA function to get the file dialog instead of using the ActiveX dialog control.

I'm still not sure why the ActiveX control wasn't available to the user, but this work-around was able to allow the user to use the Excel Workbook.

0
votes

Had this exact problem and found this solution

Close Excel.

Start Windows Explorer.

Select your system drive (usually C:)

Use the Search box to search for *.exd

Delete all the files it finds.

Start Excel again.

"Cannot insert object" error when adding ActiveX control like Microsoft DataGrid to Excel sheet