Getting a range of cells to act like radio buttons (True to revert to false if true in another cell)

%3CLINGO-SUB%20id%3D%22lingo-sub-2816327%22%20slang%3D%22en-US%22%3EGetting%20a%20range%20of%20cells%20to%20act%20like%20radio%20buttons%20(True%20to%20revert%20to%20false%20if%20true%20in%20another%20cell)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816327%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20use%20data%20validation%20to%20achieve%20something%20quite%20simple.%3CBR%20%2F%3EI%20would%20like%20to%20apply%20data%20validation%20to%20a%20range%20of%20cells%2C%20so%20that%20when%20TRUE%20is%20entered%20in%20one%20of%20the%20range%2C%20the%20rest%20revert%20to%20FALSE%20-%20like%20radio%20buttons.%3C%2FP%3E%3CP%3ECan%20this%20be%20done%3F%3CBR%20%2F%3EThanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2816327%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2816462%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20a%20range%20of%20cells%20to%20act%20like%20radio%20buttons%20(True%20to%20revert%20to%20false%20if%20true%20in%20another%20c%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2816462%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176733%22%20target%3D%22_blank%22%3E%40JamFestival%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EData%20Validation%20limits%20what%20the%20user%20can%20enter%20in%20a%20cell%2C%20but%20it%20cannot%20change%20the%20value%20of%20a%20cell%20automatically.%20You%20need%20VBA%20for%20that.%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20range%20is%20C2%3AC12.%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3CBR%20%2F%3ECopy%20the%20code%20listed%20below%20into%20the%20code%20window.%3CBR%20%2F%3EClose%20the%20Visual%20Basic%20Editor.%3CBR%20%2F%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(*.xlsm).%3CBR%20%2F%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3CBR%20%2F%3ECode%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20If%20Not%20Intersect(Range(%22C2%3AC12%22)%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Set%20rng%20%3D%20Intersect(Range(%22C2%3AC12%22)%2C%20Target)(1)%0A%20%20%20%20%20%20%20%20If%20rng.Value%20%3D%20True%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20Range(%22C2%3AC12%22).Value%20%3D%20False%0A%20%20%20%20%20%20%20%20%20%20%20%20rng.Value%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to use data validation to achieve something quite simple.
I would like to apply data validation to a range of cells, so that when TRUE is entered in one of the range, the rest revert to FALSE - like radio buttons.

Can this be done?
Thanks in advance.

2 Replies

@JamFestival 

Data Validation limits what the user can enter in a cell, but it cannot change the value of a cell automatically. You need VBA for that.

Let's say the range is C2:C12.

Right-click the sheet tab.

Select 'View Code' from the context menu.
Copy the code listed below into the code window.
Close the Visual Basic Editor.
Save the workbook as a macro-enabled workbook (*.xlsm).
Make sure that you allow macros when you open it.
Code:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    If Not Intersect(Range("C2:C12"), Target) Is Nothing Then
        Set rng = Intersect(Range("C2:C12"), Target)(1)
        If rng.Value = True Then
            Application.ScreenUpdating = False
            Application.EnableEvents = False
            Range("C2:C12").Value = False
            rng.Value = True
            Application.EnableEvents = True
            Application.ScreenUpdating = True
        End If
    End If
End Sub

@Hans Vogelaar 
Thank you so much!
I need multiple instances of this, and repeating the IF... to ENDIF... sections accomplished this.
Excellent!