mercredi 21 septembre 2016

Excel VBA - Change cell color of cell on next worksheet if checkbox is checked

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.

  1. check checkbox on 'September'!D:23
  2. color on cell D23 on the next sheet October turns blue
  3. uncheck checkbox on 'September'!D:23
  4. color on cell D23 on the next sheet October 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