Adding Checkboxes on multiple workbooks

  • Hello! First time here.

    I'm having some issues trying to add checkboxes in multiple workbooks. I created a loop to work throught all files in a specific folder, but it only works on the first file and when it goes to the next one, i get error 438, for the Caption and LinkedCell functions on my code. Hope someone can help! :)

    Obs: The problem really is with the checkbox creation and definition of its parameters. I tried the code with different simple actions like defining cell values and it works perfectly.

    Thanks for the attention!

  • Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post

    All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.

    How to use code tags

    Just highlight all of the code and press the <> in the post menu above button to add the code tags.


  • So the code consists in adding a checkbox with no label ("") on a specific cell on multiple workbooks. All workbooks have the same structure where the checkboxes are supposed to be added on the cell "L12" in a specific worksheet.

    Because i have a considerable amount of workbooks, i tried to run a code that would open all workbooks in a folder, one by one, adding the checkbox, saving and then closing it. But when i run the code, it opens the first workbook in the folder, creates the checkbox (on the right place and with the right link) and when it goes to the second file in the folder, the checkbox created is added to a random place on the worksheet and the code stop running returning the error 438 and highlighting the lines ".Caption" and ".LinkedCell".

    I hope i made it understandable. Sorry for any english mistakes.

  • Try this code for adding the CheckBox

        With Sheets("Orçamento")
            .CheckBoxes.Add(Left:=.Range("L12").Left, Top:=.Range("L12").Top, Width:=.Range("Y12").Width, Height:=.Range("L12").Height).Select
            With Selection
                .Caption = ""
                .LinkedCell = "Parâmetros!A" & 27
            End With
        End With
  • Still getting the same error. Posting the full code to see if there is something else wrong. If it helps, i can upload the set of workbooks i'm trying to run the code on.

  • So i tried something here and it worked. So, what happens is that i have more checkboxes in this targets workbooks. I tried to remove all checkboxes from the workbooks before running the code, and without the other checkboxes, it works perfectly. But this doesn't really solves my problem, because i can't just remove all checkboxes from the workbooks.

    I'm attaching 3 workbooks to see if it helps.

  • It might be that a CheckBox exists with the name CheckBox1. Try this

  • I could get your checkboxes installed in the workbooks but couldn't stop the loop when using Dir and ended up with multiple checkboxes on top of each other in the cells.

    Adapted the late binding macro from this site and came up with this which seems to work.

  • OK, I had the file name screwed up when originally using DIR

    this should work for you

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!