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:
-
Checkboxes: Force the previously activated (TRUE) checkbox to deactivate (FALSE) when the next is activated.
-
OptionButton: make every OptionButton to link to a unique cell in column T.
Much appreciated. /Mathias
Aucun commentaire:
Enregistrer un commentaire