lundi 16 décembre 2019

Forcing one active checkbox at a time

I have this sheet that displays a diagram based on data in columns P,Q,R,S when 'x' is typed into column U.

For example: When X is typed into U23, a diagram based on the data in P23-S23 is displayed. Next, I have to delete the x in U23, and type a new one for U24 to view this diagram. If I forget to delete 'x' in U 23, the previous diagram will still be shown.

My goal: To replace the deleting and typing of the 'x' with a button linked to cell in row T. with only one active checkbox/button at the same time.

Problem: My VBA allows multiple checkboxes to be activated (TRUE) simultaneously.

This is my code so far (not my work):

Sub AddCheckBoxes()
    On Error Resume Next
    Dim c As Range, myRange As Range
    Set myRange = Selection
    For Each c In myRange.Cells
        ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
            With Selection
                .LinkedCell = c.Address
                .Characters.Text = ""
                .name = c.Address
            End With
            c.Select
            With Selection
                .FormatConditions.Delete
                .FormatConditions.Add Type:=xlExpression, _
                    Formula1:="=" & c.Address & "=x"

                .FormatConditions(1).Font.ColorIndex = 6 'change for other color when ticked
                .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked
                .Font.ColorIndex = 2 'cell background color = White
            End With
        Next
        myRange.Select
End Sub

I've tried replacing "ActiveSheet.CheckBoxes.Add" with "ActiveSheet.OptionButtons.Add" but every OptionButton gets linked to the same cell.

So I would like any of these results:

  1. Checkboxes: Force the previously activated (TRUE) checkbox to deactivate (FALSE) when the next is activated.

  2. OptionButton: make every OptionButton to link to a unique cell in column T.

Much appreciated. /Mathias




Aucun commentaire:

Enregistrer un commentaire