CODEWITHSUNDEEP
vbaexcelcheckbox
user3185735
user3185735

Reputation: 223

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?

Upvotes: 2

Views: 623

Answers (2)

user3598756
user3598756

Reputation: 29421

edited NextMonthSht() to properly handle months index after 9

Here follows a slightly different solution to:

  • allow for months sheets in any index order

  • allow for any sheets name

Code:

Sub CheckedUnchecked()
    With ActiveSheet.CheckBoxes(Application.Caller)
        NextMonthSht.Range(.TopLeftCell.Address).Interior.ColorIndex = IIf(.Value = 1, 5, 3)
    End With
End Sub

Function NextMonthSht() As Worksheet
    Const MONTHS As String = "January,February,March,April,Maj,June,July,August,September,October,November,December,January"

    Set NextMonthSht = Worksheets(Split(MONTHS, ",")(Len(Left(MONTHS, InStr(MONTHS, ActiveSheet.name))) - Len(Replace(Left(MONTHS, InStr(MONTHS, ActiveSheet.name)), ",", "")) + 1))
End Function

Upvotes: 2

user6432984
user6432984

Reputation:

Worksheets have a .Next method that references the next worksheet in the worksheets collection. If the worksheet is the last worksheet in the collection it will return nothing.

enter image description here

Paste into a standard module

Sub CheckedUnchecked()
    Dim cb As CheckBox
    With ActiveSheet
        Set cb = .CheckBoxes(Application.Caller)
        If Not cb Is Nothing Then
            If .Next Is Nothing Then
                Worksheets("January").Range(cb.TopLeftCell.Address).Interior.ColorIndex = IIf(cb.Value = 1, 5, 3)
            Else
                .Next.Range(cb.TopLeftCell.Address).Interior.ColorIndex = IIf(cb.Value = 1, 5, 3)
            End If
        End If
    End With
End Sub

Upvotes: 2

Related Questions