importing results from a checkbox

[Gti182]

I'm working with a macro to import specific cell data from many single standardised excel files into a master file.

Problem im having is with these single files contain "Yes" or "No" checkboxes and i require the answer (either yes or no) to pull through into the master file.

Is this possible as the checkboxes aren't physically in the cell, is there perhaps a simpler way im missing?

inserted current code below (only relevant sections included)

With Application.FileDialog(msoFileDialogFolderPicker) ' Select folder where project files are saved
    .ButtonName = "OK"
    .Title = "Select folder containing files to consolidate"
    .AllowMultiSelect = False
    .Show
    On Error Resume Next
    Path = .SelectedItems(1)
    Err.Clear
    On Error GoTo 0
End With
If Path = Empty Then MsgBox "Macro cancelled.": Exit Sub       ' If message box is cancelled exit macro

If Right(Path, 1) <> "\" Then Path = Path & "\"

FileName = Dir(Path & "*.xlsx")
Do While FileName <> ""
    Workbooks.Open FileName:=Path & FileName, ReadOnly:=True
    For Each Sh In ActiveWorkbook.Worksheets
    If Sh.Range("A5").Value = "Project" Then               'If cell A5 contains the text "Project" then copy data from that sheet
        MyArray(0) = Sh.Range("B4").Value       
        MyArray(1) = Sh.Range("B5").Value      
        MyArray(2) = Sh.Range("B6").Value      
        MyArray(3) = Sh.Range("B7").Value       
        MyArray(4) = Sh.Range("B8").Value       
        MyArray(5) = Sh.Range("B9").Value     
        MyArray(6) = Sh.Range("B10").Value     
        MyArray(7) = Sh.Range("B11").Value      
        MyArray(8) = Sh.Range("B12").Value    
        MyArray(9) = Sh.Range("B13").Value      
        MyArray(10) = Sh.Range("B14").Value     
        MyArray(11) = Sh.Range("B15").Value     
        MyArray(12) = Sh.Range("B16").Value    
        MyArray(13) = Sh.Range("B17").Value     
        MyArray(14) = Sh.Range("B22").Value     
        MyArray(15) = Sh.Range("B24").Value    
        MyArray(16) = Sh.Range("B25").Value     
        MyArray(17) = Sh.Range("B26").Value     
        MyArray(18) = Sh.Range("B27").Value     
        MyArray(19) = Sh.Range("B28").Value     
        MyArray(20) = Sh.Range("B29").Value    
        MyArray(21) = Sh.Range("B30").Value     
        MyArray(22) = Sh.Range("B31").Value    ' checkbox 1
        MyArray(23) = Sh.Range("B32").Value    ' checkbox 2
        MyArray(24) = Sh.Range("B33").Value    ' checkbox 3
        MyArray(25) = Sh.Range("B35").Value     
        MyArray(26) = Sh.Range("B36").Value  
        MyArray(27) = Sh.Range("B37").Value   
        MyArray(28) = Sh.Range("B39").Value    
        MyArray(29) = Sh.Range("B40").Value   
                     
        LR = ThisWorkbook.Sheets(MainSh).Range("A" & Rows.Count).End(xlUp).Row + 1 ' calculate the last row of the data range
        For i = 0 To 29     ' Number of columns in the range
            ThisWorkbook.Sheets(MainSh).Cells(LR, i + 1).Value = MyArray(i)
        Next i
        
    ThisWorkbook.Sheets(MainSh).Hyperlinks.Add _
    Anchor:=ThisWorkbook.Sheets(MainSh).Range("A" & LR), Address:=Path & FileName    ' Add hyperlinks to column A to link to project files

    End If
    Next Sh
    ActiveWorkbook.Close False
    FileName = Dir()
Loop


End Sub

[Norie]

Are the checkboxes linked to cells?

[Gti182]

they are not linked no. they are simple form control checkboxes

[Norie]

You'll need to use the Shapes collection of the worksheet(s) to get the values if the checkbox(es).

PS Forms chevkboxes can have linked cells.

[Gti182]

that sounds interesting Norie, can you elaborate a bit more not following 100%

[Norie]

This will loop through all the shapes on Sheet1, if they are checkboxes it will output their value - 1 for checked and -4146 for unchecked.

For Each chk In Sheets("Sheet1").Shapes

    If TypeName(chk.OLEFormat.Object) = "CheckBox" Then
        MsgBox chk.OLEFormat.Object.Value
    End If

Next chk

[Gti182]

thats awesome! Ok i see how it works now but a bit unsure on how to incorporate it into my code, any tips?

[Norie]

I don't really know too much about the sheets you are dealing with but if you always want the values from specific checkboxes you coudl use there names.

MyArray(22) = Iif(Sh.Shapes("CheckBox 1").OLEFormat.Object.Value = 1, "Yes", "No")    ' checkbox 1

[Gti182]

ok i follow that, inserted the above code but getting a runtime error -2147024809 (80070057) the item with the specified name was not found

[Norie]

Have you checked the name?

Is it definitely a Forms checkbox?

Can you upload an example workbook?

Click on GO ADVANCED and use the paperclip icon to open the upload window.

[Gti182]

template file attached.

not sure how to check the checkbox name

[Norie]

Select the checkbox and it's name will appear in the name box next to the formula bar.

[Gti182]

i put a space between Check and Box works 100% now thanks so much for the help!

[Gti182]

great, see it now

[Norie]

Glad it worked.