CODEWITHSUNDEEP
excelvbaexcel-2016
EmmaN
EmmaN

Reputation: 23

How to reference a check box in VBA for Excel

I created a check box using the Developer Ribbon > Insert > ActiveX Controls > Check Box. I would like to code a sub which, when the box is checked, a range of values from the PCAPV10 worksheet is copied into a range on the BOM worksheet. I'm not sure if I am referencing my check box correctly in the code, and keep getting an object error. Any suggestions?

I've tried coding within the check box sub, and also referencing the check box from another sub. I can't seem to figure out either way to do this.

Private Sub CheckBox1_Click()
Dim PCAPV10 As Worksheet
Set PCAPV10 = Workbooks("CFC Calculation Program (Macro Enabled)").Sheets("Price Calculation APV10")
Dim BOM As Worksheet
Set BOM = Workbooks("CFC Calculation Program (Macro Enabled)").Sheets("BOM")
Dim chk1 As CheckBox
Set chk1 = Sheets("Price Calculation APV10").CheckBoxes("Check Box 1")
If Sheets("PCAPV10").OLEObjects("chk1").Object.Value = True Then
BOM.Range("A6:C120").Value = PCAPV10.Range("E11:I84").Value
End If
End Sub

I would like the code to recognize when the box is checked, and if true set the range within the BOM sheet equal to the range within the PCAPV10 sheet. If unchecked, do nothing. Instead, I am receiving the Run-time error '1004': Application-defined or Object-defined error.

Upvotes: 2

Views: 12723

Answers (3)

David Podolak
David Podolak

Reputation: 195

No need to declare CheckBox1 As A Variable. You also put your variable PCAPV10 in Sheets(""). Try This:

If PCAPV10.OLEObjects("CheckBox1").Object.Value = True Then
     BOM.Range("A6:C120").Value = PCAPV10.Range("E11:I84").Value
End If

If You Want To Define It, I'm Pretty Sure there isn't spaces:

Set chk1 = Sheets("Price Calculation APV10").CheckBoxes("CheckBox1")

Upvotes: 3

Darrell H
Darrell H

Reputation: 1886

You have already declared the check box and set the check box. Simply change the following:

If chk1.Value= 1 Then

Upvotes: 1

Matt Cottrill
Matt Cottrill

Reputation: 148

This may not be the best practice, but I have always linked the check box to the cell it is in, so that cell toggles TRUE / FALSE.

Then I make the text match the fill color so it is not visible to the user.

Then in VBA I just use .range() or .cell() to match to that cell.

So I would use:

If Sheets("PCAPV10").range()

instead of what you have.

Upvotes: 0

Related Questions