Adding Checkboxes on multiple workbooks

Prospect324 · Aug 17th 2021

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.

Code

Sub LoopThroughFiles()


folderName = "C:\******"
If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
Fname = Dir(folderName & "*.xlsx")


'loop through the files
Do While Len(Fname)


With Workbooks.Open(folderName & Fname)




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


ActiveWorkbook.Close savechanges:=True
' go to the next file in the folder
Fname = Dir


Loop


End Sub

Display More

Thanks for the attention!

**royUK** · Aug 18th 2021

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.

Thanks.

**royUK** · Aug 18th 2021

Read the Forum Rules and then tell us exactly what is not working

Prospect324 · Aug 18th 2021

Thanks for the instructions Roy!

Sorry for not reading the Forum Rules before posting my problem.

**royUK** · Aug 18th 2021

Can you explain what is not happening. The code to add a Checkbox works fine on a single sheet.

Prospect324 · Aug 18th 2021

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.

**royUK** · Aug 19th 2021

Try this code for adding the CheckBox

Code

    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

Prospect324 · Aug 19th 2021

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.

Code

Sub LoopThroughFiles()
    
    folderName = "C:\Users\BSCAMPO1\Desktop\Orçamentos Geral"
    If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator
    Fname = Dir(folderName & "*.xlsx")

    'loop through the files
    Do While Len(Fname)

        With Workbooks.Open(folderName & Fname)
            
            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

        
        End With
        
        ActiveWorkbook.Close savechanges:=True
        ' go to the next file in the folder
        Fname = Dir

    Loop
    
End Sub

Display More

**royUK** · Aug 19th 2021

Remove the Caption part, if that doesn't work attach a couple of the target workbooks.

Prospect324 · Aug 19th 2021

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.

**royUK** · Aug 20th 2021

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

Code

Sub LoopThroughFiles()
    
    FolderName = "C:\Users\BSCAMPO1\Desktop\Orçamentos Geral"
    If Right(FolderName, 1) <> Application.PathSeparator Then FolderName = FolderName & Application.PathSeparator
    Fname = Dir(FolderName & "*.xlsx")
    'loop through the files
    Do While Len(Fname)
        With Workbooks.Open(FolderName & Fname)
            
            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
                    .Name = "XYZ"
                End With
            End With
            
        End With
        
        ActiveWorkbook.Close savechanges:=True
        ' go to the next file in the folder
        Fname = Dir
    Loop
    
End Sub

Display More

Prospect324 · Aug 26th 2021

Unfortunately it still doesn't work properly. Same problem with the "Caption" line.

Thanks for the help anyway!

NoSparks · Aug 27th 2021

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.

Code

Sub LoopAllFilesInFolder()

Dim folderName As String
Dim FSOLibrary As Object
Dim FSOFolder As Object
Dim FSOFile As Object
Dim chk As CheckBox

'Set the folder name to a variable
folderName = "D:\OZtesting\"        '<~~~~~~~~~ CHANGE TO YOUR SPECIFIC FOLDER

'Set all the references to the FSO Library
Set FSOLibrary = CreateObject("Scripting.FileSystemObject")
Set FSOFolder = FSOLibrary.GetFolder(folderName)
Set FSOFile = FSOFolder.Files

'Use For Each loop to loop through each file in the folder
For Each FSOFile In FSOFile
If Right(FSOFile.Name, 4) = "xlsx" Then
With Workbooks.Open(FSOFile)
With .Sheets("Orçamento")
Set chk = .CheckBoxes.Add(Left:=.Range("L12").Left, Top:=.Range("L12").Top, Width:=.Range("Y12").Width, Height:=.Range("L12").Height)
End With
With chk
.Caption = ""
.LinkedCell = "Parâmetros!A" & 27
End With
.Close savechanges:=True
End With
End If
Next

'Release the memory
Set FSOLibrary = Nothing
Set FSOFolder = Nothing
Set FSOFile = Nothing

End Sub

Display More

NoSparks · Aug 27th 2021

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

this should work for you

Code

Sub LoopThroughFiles()

Dim chk As CheckBox

folderName = "C:\Users\BSCAMPO1\Desktop\Orçamentos Geral"
If Right(folderName, 1) <> Application.PathSeparator Then folderName = folderName & Application.PathSeparator

Fname = Dir(folderName & "*.xlsx")

'loop through the files
Do While Len(Fname)
With Workbooks.Open(folderName & Fname)
With .Sheets("Orçamento")
Set chk = .CheckBoxes.Add(Left:=.Range("L12").Left, Top:=.Range("L12").Top, Width:=.Range("Y12").Width, Height:=.Range("L12").Height)
End With
With chk
.Caption = ""
.LinkedCell = "Parâmetros!A" & 27
End With
.Close savechanges:=True
End With
Fname = Dir
Loop
End Sub

Display More

Adding Checkboxes on multiple workbooks

Participate now!

Share