dimanche 2 mai 2021

How to Loop Through ActiveX Checkboxes on VBA

I'm actually trying to make a userform that controls every checkbox in the Workbook Sheets (it's mostly graphs that shows the data from a specific period for the Company), since every graph has the same checkboxes (ActiveX ones) with the same names on them I though about making a Userform that is always active and in this way the client can just select which ones he will use and it would just loop through the other sheets without a problem.

But here is the problem first of all my code:

Dim ws As Worksheet

If Me.CheckBox1.Value = True Then
    For Each ws In ThisWorkbook.Worksheets
        ws.OLEObjects("chkAno1").Object.Value = 1
    Next ws

ElseIf Me.CheckBox1.Value = False Then
    For Each ws In ThisWorkbook.Worksheets
        ws.OLEObjects("chkAno1").Object.Value = 0
    Next ws

End If

I did it with only one checkbox to test it out (There is a "chkAno1" in every sheet that I want to affect) but everytime I run the code and click the checkbox I get " Error 1004: The Method "OLEObjects" from object "_Worksheet" Failed", and what's weird is that If I change "ws" with "ActiveSheet" the code works fine, but only updates the currently open sheet.

So I'm at a loss right now.




Aucun commentaire:

Enregistrer un commentaire