Ticking check boxes in other workbooks with code

[Will S]

I'm not 100% sure if this is possible but, what I'm trying to do is have one sheet, read the value of a cell that will correspond with the name of the check box in a different workbook and tick that check box.

So far my code is

Private Sub CreateTicks_Click()
Dim f As String
Dim ff As String
f = Range("AF548").Value


ff = "Checkbox" & f




    Workbooks.Open Filename:=ThisWorkbook.Path & "Othersheet.xlsm"
    Windows("Othersheet.xlsm").Activate
    
ActiveSheet.CheckBoxes(ff).Value = xlOn
End Sub

When I run it I get a run-time error "Unable to get the CheckBoxes property of the Worksheet Class" and quite frankly I have no idea of what that means...

I've tried it with both ActiveX and basic Form

With ActiveX it works if I have :

ActiveSheet.CheckBoxE550.Value = True

But that doesn't allow me to have a dynamicly named as ff.value = True causes an error.

IN SHORT
Is there a way to Dim something as the name of an activeX Check box, or a way to tick standard form check boxes with code in a different workbook?

 

[Andrew Poulsom]

For an ActiveX Checkbox:

ActiveSheet.OLEObjects(ff).Object.Value = True

 

[mohan.pandey87]

May be like this:

Option Explicit


Sub lmp_Test()


    Dim wbkChkFile          As Workbook
        
    Set wbkChkFile = Workbooks.Open("D:\Users\llm82928\Desktop\Sol.xlsm", False)
    
    With wbkChkFile.Worksheets("Sheet1")
        .CheckBoxes("chkTest1").Value = True 'Form control
        .OLEObjects("chkTest2").Object.Value = True 'Activex Control
    End With
    
    wbkChkFile.Close 0


End Sub

 

[Will S]

Thanks for the help and quick responses guys, it's still coming up with errors though...

I changed the original button to call a "public" sub

Sub Fill()


Dim CertFile As Workbook
Set CertFile = Workbooks.Open(ThisWorkbook.Path & "Othersheet.xlsm", False)


f = Range("AF548").Value


With CertFile.Worksheets("Sheet1")
.OLEObjects(f).Object.Value = True
End With


End Sub

This comes with "Automation Error"

Trying it within the "on click" stuff brings up either the same error but saying it can't read the "OLEObject" property or that the object is out of range.

I am assuming it's screwing up with affecting stuff in a different file but can't see a way around it.

Best regards
~Will S

 

[Andrew Poulsom]

Isn't it?

.OLEObjects("Checkbox" & f)

And the assignment of f should take place before the workbook is opened, because your unqualified Range property will use the ActiveSheet.

 

[Will S]

While messing around with things I changed the value of AF548 to include the word "Checkbox" but thank you! It works fine now.