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
Bookmarks