1

I am using Excel 2013. I have an excel workbook. The workbook contains an activex checkbox control in the sheet 'charts'. The control is called chkAll. When the workbook is opened some default settings are applied one of which is to make this checkbox checked. The line of code below is what I am using. 

 Sheets("charts").chkAll.value = true

This has worked for the past few months but today it is no longer working. Get the error message "Object doesn't support this property or method".

Also when I manually try to add a checkbox to the spreadsheet from the the ActiveX Control section I get an error message saying I "cannot insert object".

Not sure why these error are now happening?

mHelpMe
  • 5,782
  • 15
  • 59
  • 109
  • 2
    Sounds like you've been hit by this: http://excelmatters.com/2014/12/10/office-update-breaks-activex-controls/ – Rory Jan 02 '15 at 08:54
  • 1
    thanks Rory that has fixed the issue. Would have spent ages trying to find that! If you repost your comment as an answer would be happy to mark it as the answer – mHelpMe Jan 02 '15 at 09:13

2 Answers2

3

This is caused by a Microsoft update that prevents ActiveX controls working in Excel workbooks. Closing all office programs, then navigating to:

C:\Users\[username]\AppData\Local\Temp\Excel8.0

and deleting the MSForms.exd file will resolve the issue.

This is a temp file and will be re-created when you launch Excel again so don't worry about deleting it.

SierraOscar
  • 16,918
  • 4
  • 36
  • 59
0

try this (for ActiveX control inside a worksheet) :

ThisWorkbook.Sheets("charts").Shapes("chkAll").OLEFormat.Object.Value = 1 '= true // for false the value is -4146.

Also, for a Form Checkbox inside a sheet use :

ThisWorkbook.Sheets("charts").Shapes("chkAll").ControlFormat.Value = 1 '= true // for false the value is 0.
Patrick Lepelletier
  • 1,416
  • 2
  • 15
  • 22