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
D23on the next sheetOctoberturns blue - uncheck checkbox on
'September'!D:23 - color on cell
D23on the next sheetOctoberturns 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