Auto generation of lists from check-boxes on other pages

M

Myles

New member
Joined
Apr 10, 2013
Messages
1
Reaction score
0
Points
0
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.
 
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.
 
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
 
You must log in or register to reply here.
Back
Top