auto added checkbox

[niailmar]

Hello everyone. I wanna ask about this problem:


I have a table of data(Sheet1), which all the data will retrieve from physical folder (C:/Data).
(as attach image)


My problem are:


1) When new data retrieved, a checkbox will automatically add, at the end of the column. The new data will be inserted row by row.
2) When the checkbox is ticked, send the ticked data to another sheet (Sheet2).
At the Sheet2, there have two column. So, both of two column is need to be automatically ticked when the checkbox at Sheet1 have ticked.




I appreciate your help to solve my problem.

 

[Bob Phillips]

How is the data added? By code? If so, post that code.

 

[niailmar]

hi Bob, here this code.

Sub ReadDataFromAllWorkbooksInFolder()Dim FolderName As String, wbName As String, r As Long, cValue As Variant
Dim wbList() As String, wbCount As Integer, I As Integer
Dim a, b, C, d, e, f, g, h, j, k, l, m, n As String
'Obj As OLEObject
    






    FolderName = "C:\PMCare Automation Report\Historical\PC"
    ' create list of workbooks in foldername
    wbCount = 0
    wbName = Dir(FolderName & "\" & "*.xls")
    While wbName <> ""
    
        wbCount = wbCount + 1
        ReDim Preserve wbList(1 To wbCount)
        wbList(wbCount) = wbName
        wbName = Dir
    Wend
    If wbCount = 0 Then Exit Sub
    ' get values from each workbook
    r = 11
    
         Worksheets("Personal Claim").Visible = xlSheetVisible
         Sheets("Personal Claim").Select
                   
    
       
    For I = 1 To wbCount
        r = r + 1
          
        cValue = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "E61")
        
        'edit
        
        a = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B60")
        b = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B68")
        C = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "G69")
        
        If a = b Then
        d = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "C70")
        'Exit For
        Else
        d = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "C100")
        End If
        
        
        e = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B72")
        f = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "B75")
        g = GetInfoFromClosedFile(FolderName, wbList(I), "PCForm", "D74")


   
        ActiveSheet.Unprotect
        Cells(r, 3).Formula = cValue
        Cells(r, 4).Formula = a
        Cells(r, 5).Formula = b
        Cells(r, 6).Formula = C
     
        Cells(r, 7).Formula = d
        Cells(r, 8).Formula = e
        Cells(r, 9).Formula = f
        Cells(r, 10).Formula = g
        Cells(r, 11).Formula = "=HYPERLINK(""" & FolderName & "\" & wbList(I) & """, """ & wbList(I) & """)"
'         Cells(r, 12).Formula = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", Link:=False, _
'        DisplayAsIcon:=False, Left:=1584.75, Top:=1400.25, Width:=14.25, _
'        Height:=10.5).Select
'    Selection.ShapeRange.IncrementLeft 12#
'    Selection.ShapeRange.IncrementTop -1.5






    
     ActiveSheet.Protect


    Next I


End Sub

 

[richarrd]

If I understand correctly... I think you need some code in the worksheet change event.

This event will fire when you add the new rows you describe.

You can code the change event to look at column 12 for the changed row and if the cell does not not contain a checkbox - then add one using the same method you show in your code.