Results 1 to 3 of 3

Thread: Auto generation of lists from check-boxes on other pages

  1. #1

    Auto generation of lists from check-boxes on other pages



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

    I have a list on a sheet, (Client Number, Surname), as well as check-boxes for gym sessions (AM1, AM2, AM3, AM4, PM1, PM2, PM3, PM4) in rows.

    If checkboxes for AM1 and PM3 (for example) are checked, I want the client number and surname to appear on the sheets named AM1 and PM3 respectively.

    I'm working on a list of about 300 people, and want to be able to simply check the sessions next to their names and have the respective session sheets seeded live as it were.

    I would be grateful of any assistance as I'm a touch clueless.

    Thanks in advance.

  2. #2
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.


    To attach a file to your post, you need to be using the "Reply to Thread', not the "Quick Reply" button.


    On this page, below the message box, you will find a button labelled 'Manage Attachments'.
    Clicking this button will open a new window for uploading attachments.


    You can upload an attachment either from your computer or from another URL by using the appropriate box on this page.
    Alternatively you can click the Attachment Icon to open this page.


    To upload a file from your computer, click the 'Browse' button and locate the file.


    To upload a file from another URL, enter the full URL for the file in the second box on this page.
    Once you have completed one of the boxes, click 'Upload'.


    Once the upload is completed the file name will appear below the input boxes in this window.
    You can then close the window to return to the new post screen.
    Hope that helps

    Roy

  3. #3
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    718
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Not surprised no sample workbook posted. 8 different sessions times 300 clients would be 2400 checkboxes on the sheet.

    A routine I use continually crashed my Excel 2010 somewhere around 1300 checkboxes every time I tried to put that many on a sheet.

    Solution was to just put boarders around cells and use the Worksheet_BeforeDoubleClick event to toggle the cells with the WingDing check mark.

    Use VBA similiar to this in the sheet1 code

    Code:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        
        Dim MyRng As Range
        Dim LastRow As Long
        
    Cancel = True
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    Set MyRng = Range("D2:K" & LastRow)
    
    If Not Intersect(Target, MyRng) Is Nothing Then
        With Target
            With Selection.Font
                .Name = "Wingdings"
                .Size = 14
        End With
        
            If .Value = "" Then
                .Value = ""
            Else
                .Value = ""
            End If
        End With
    End If
    
    Call CopyFilteredToOtherSheet
    
    End Sub

    And put a procedure like this in a module

    Code:
    Sub CopyFilteredToOtherSheet()
        
        Dim LastRow As Long
        Dim CurRow As Long
        Dim CurCol As Long
        Dim SheetToChange As String
        
    Application.ScreenUpdating = False
        
        Sheets("Sheet1").Select
    ' gather info related to sheet1
        LastRow = Cells(Rows.Count, "B").End(xlUp).Row
        CurRow = ActiveCell.Row
        CurCol = ActiveCell.Column
        SheetToChange = Cells(1, CurCol).Value
        
    ' goto the copy to sheet
        Sheets(SheetToChange).Select
        With ActiveSheet
            .UsedRange.ClearContents
            .Range("A1").Select
        
        'Sheets("Sheet1").Range("A1:K" & LastRow).Select
        Sheets("Sheet1").Range("A1:K" & LastRow).AutoFilter field:=CurCol, Criteria1:="<>"
            
        'Range("A:B").Select
        Sheets("Sheet1").Range("A:B").Copy
            .Paste
            .Range("A1").Select
        End With
        
        Sheets("Sheet1").Activate
        Application.CutCopyMode = False
        Sheets("Sheet1").Range("A1:K" & LastRow).AutoFilter
        Cells(CurRow, CurCol).Select
    
    Application.ScreenUpdating = True
    
    End Sub

    Let me know if this works for you

    NoS

Posting Permissions

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