CODEWITHSUNDEEP
vbaexcelactivex
mHelpMe
mHelpMe

Reputation: 6668

using activex checkbox control in spreadsheet

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?

Upvotes: 1

Views: 1374

Answers (2)

Patrick Lepelletier
Patrick Lepelletier

Reputation: 1652

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.

Upvotes: 0

SierraOscar
SierraOscar

Reputation: 17647

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.

Upvotes: 3

Related Questions