I have an excel document with sheets representing information for each month. These sheets contain lists of members. On each sheet there are multiple checkboxes (about 250 per sheet). I would like to do the following.
Lets take months September
and October
as an example. if I check a checkbox on the September
-sheet then the color of the same cell the checkbox is located (ex. D23
) on the next sheet (in this case it would be "October"
) should change to blue. If the same checkbox is being unchecked (even for example in the future) then the cell should turn red.
- check checkbox on
'September'!D:23
- color on cell
D23
on the next sheetOctober
turns blue - uncheck checkbox on
'September'!D:23
- color on cell
D23
on the next sheetOctober
turns red
My way of solving the problem: I have written a Sub
which, when a checkbox is created, it links the checkbox to the cell it was placed on (for example D23
) so that I can know which cell needs to change when checked/unchecked on the next sheet:
.LinkedCell = Selection.Address(False, False)
This is what I've tried so far but with no success:
Sub SetMacro()
Dim cb
For Each cb In ActiveSheet.CheckBoxes
If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
Next cb
End Sub
and here is the code for the check/uncheck:
Sub CheckedUnchecked()
With ActiveSheet.Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell)
If .Value Then
Worksheet(ActiveSheet.Index + 1).Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Interior.ColorIndex = 5
Else
Worksheet(ActiveSheet.Index + 1).Range(ActiveSheet.CheckBoxes(Application.Caller).LinkedCell).Interior.ColorIndex = 3
End If
End With
End Sub
Any thoughts on this matter?
Aucun commentaire:
Enregistrer un commentaire