CODEWITHSUNDEEP
excelvbaactivex
Michael T
Michael T

Reputation: 1955

How to refer to ActiveX checkboxes on another worksheet by name within a variable

I have 57 checkboxes on one worksheet and I want code to check or uncheck 57 checkboxes on another worksheet which all have the same names (chk01, chk02, etc). Once I get the name of the checkbox from the first sheet, I can't figure out how to make it check the same checkbox on the other sheet.

I get an error "Object doesn't support this property or method" on the "ws.OLEObjects(checkboxName).Value = ws2.OLEObjects(checkboxName).Value" line.

Sub DeploymentPrep_Output_Create()

Dim ws, ws2 As Worksheet
Dim oj As OLEObject
Dim checkboxName As String

Set ws = Worksheets("Output")
Set ws2 = Worksheets("Inputs")
'Cycle through all of the check boxes in the ActiveSheet
For Each oj In ws.OLEObjects
If TypeName(oj.Object) = "CheckBox" Then
    checkboxName = oj.Name
    ws.OLEObjects(checkboxName).Value = ws2.OLEObjects(checkboxName).Value
End If

Next oj

End Sub

Upvotes: 0

Views: 30

Answers (1)

user3598756
user3598756

Reputation: 29421

the Value property is to be called on the Object member of the OLEObject object

If TypeName(oj.Object) = "CheckBox" Then
    checkboxName = oj.Name
    oj.Object.Value = ws2.OLEObjects(checkboxName).Object.Value
End If

Upvotes: 1

Related Questions