Results 1 to 4 of 4

Thread: auto added checkbox

  1. #1

    Question auto added checkbox

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Click image for larger version. 

Name:	check and send data.JPG 
Views:	111 
Size:	25.7 KB 
ID:	686Hello 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.
    Last edited by niailmar; 2012-07-03 at 09:33 AM. Reason: add image

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Excel Version
    How is the data added? By code? If so, post that code.

  3. #3
    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
        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")
            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
            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")
            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
        Next I
    End Sub

  4. #4
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts