asked on

excel reference checkbox, vba

Dear experts,

i think it should be very simple to reference a checkbox but i don't seem to get it to work. i want to reference a checkbox on a excelsheet. below is my code

Sub CheckBox1_Click()

If Workbooks("Book1").Worksheets("Deals Calc").CheckBox1 = True Then
Workbooks("Book1").Worksheets("Settings").Range("L1").Value = 1

Else
Workbooks("Book1").Worksheets("Settings").Range("L1").Value = 13
End If

End Sub

Open in new window

i get runtime error 438

cheers,

Mark

EricLynnWright

you look to be referencing your control correctly. However, it's probably the path build up to the control. Check the name of the control - is it checkbox1. Check the spelling of the worksheet and make sure it's ok.

and did you save the workbook to a different name (other than book1)?

EricLynnWright

might change this ...

If Workbooks("Book1").Worksheets("Deals Calc").CheckBox1

to...

If activeworkbook.activesheet.CheckBox1

MarkVrenken

ASKER

This is really stupid but i don't know where i can find/change the name of the control

The name of the workbook is referenced the right way, but i changed the name to book1 before posting it here

MarkVrenken

ASKER

changing it to If activeworkbook.activesheet.CheckBox1 doesn't make a difference

MarkVrenken

ASKER

i think it's in the upper left corner next to the formula bar right? that says CheckBox1

MarkVrenken

ASKER

ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Object.Value is working.

Norie

If the code is in the module of the worksheet that the control is on you can just use the name, CheckBox1.

MarkVrenken

ASKER

does anyone have an idea of why it only works like i do it?

ASKER CERTIFIED SOLUTION

EricLynnWright

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MarkVrenken

ASKER

Thanks:)

Norie

I added a Forms checkbox and ActiveX checkbox to the same worksheet, not at the same time.

This only worked with the ActiveX checkbox:

ActiveSheet.Shapes("Checkbox1").OLEFormat.Object.Object.Value

Open in new window

When I tried it with the Forms checkbox it result in an 'Object doesn't support this property or method' error.

Like I said, if the code is in the same worksheet as the worksheet the checkbox is on you can just use it's name - you even get intellisense.

If you are using a Forms checkbox then you can use the sheets Checkboxes collection - see the attached file.
Checkboxes.xlsm